Printable View
Hi,
I know I have exec to do things like :
DECLARE @str varchar(3000)
DECLARE @tbl varchar(128)
SET @tbl = 'myTable' -- assume I get that info from somewhere
SET @str = 'SELECT * FROM ' + ltrim(@tbl)
EXEC @str
BUT : what if I want to do something like : select @myvar = field1 from @tbl ?
I cannot pass @myvar to the exec - other scope.
I cannot write it just plain ,
Any idea ?
eyal
You could create a temp table to hold the results
INSERT INTO #temp
EXEC (@str)
Then select from #temp to set the variable
------------
Eyal Peleg at 9/25/00 4:11:27 PM
Hi,
I know I have exec to do things like :
DECLARE @str varchar(3000)
DECLARE @tbl varchar(128)
SET @tbl = 'myTable' -- assume I get that info from somewhere
SET @str = 'SELECT * FROM ' + ltrim(@tbl)
EXEC @str
BUT : what if I want to do something like : select @myvar = field1 from @tbl ?
I cannot pass @myvar to the exec - other scope.
I cannot write it just plain ,
Any idea ?
eyal
Kristine ,
I see you're just waiting for my questions,and now I'll owe you another penny.
I managed to get the result by dynamically declaring a cursor, in short :
exec ('declare mycursor CURSOR for select field1 from '+@tbl)
open mycursor
fetch next from mycursor into @myvar
...
do you think it's worse/better than the temp table ?
Eyal.
BTW , in SQL 2000 , working with table variables , with identity columns and a while on that column , instead of using cursors - saves a lot of memory ...
------------
Kristine Greenlee at 9/25/00 4:35:49 PM
You could create a temp table to hold the results
INSERT INTO #temp
EXEC (@str)
Then select from #temp to set the variable
------------
Eyal Peleg at 9/25/00 4:11:27 PM
Hi,
I know I have exec to do things like :
DECLARE @str varchar(3000)
DECLARE @tbl varchar(128)
SET @tbl = 'myTable' -- assume I get that info from somewhere
SET @str = 'SELECT * FROM ' + ltrim(@tbl)
EXEC @str
BUT : what if I want to do something like : select @myvar = field1 from @tbl ?
I cannot pass @myvar to the exec - other scope.
I cannot write it just plain ,
Any idea ?
eyal
If the code that you listed works, then you are declaring a global cursor. So, I would think that a local temp table would be better.
------------
Eyal Peleg at 9/25/00 4:43:14 PM
Kristine ,
I see you're just waiting for my questions,and now I'll owe you another penny.
I managed to get the result by dynamically declaring a cursor, in short :
exec ('declare mycursor CURSOR for select field1 from '+@tbl)
open mycursor
fetch next from mycursor into @myvar
...
do you think it's worse/better than the temp table ?
Eyal.
BTW , in SQL 2000 , working with table variables , with identity columns and a while on that column , instead of using cursors - saves a lot of memory ...
------------
Kristine Greenlee at 9/25/00 4:35:49 PM
You could create a temp table to hold the results
INSERT INTO #temp
EXEC (@str)
Then select from #temp to set the variable
------------
Eyal Peleg at 9/25/00 4:11:27 PM
Hi,
I know I have exec to do things like :
DECLARE @str varchar(3000)
DECLARE @tbl varchar(128)
SET @tbl = 'myTable' -- assume I get that info from somewhere
SET @str = 'SELECT * FROM ' + ltrim(@tbl)
EXEC @str
BUT : what if I want to do something like : select @myvar = field1 from @tbl ?
I cannot pass @myvar to the exec - other scope.
I cannot write it just plain ,
Any idea ?
eyal
------------
Kristine Greenlee at 9/25/00 4:46:48 PM
If the code that you listed works, then you are declaring a global cursor. So, I would think that a local temp table would be better.
------------
Eyal Peleg at 9/25/00 4:43:14 PM
Kristine ,
I see you're just waiting for my questions,and now I'll owe you another penny.
I managed to get the result by dynamically declaring a cursor, in short :
exec ('declare mycursor CURSOR for select field1 from '+@tbl)
open mycursor
fetch next from mycursor into @myvar
...
do you think it's worse/better than the temp table ?
Eyal.
BTW , in SQL 2000 , working with table variables , with identity columns and a while on that column , instead of using cursors - saves a lot of memory ...
------------
Kristine Greenlee at 9/25/00 4:35:49 PM
You could create a temp table to hold the results
INSERT INTO #temp
EXEC (@str)
Then select from #temp to set the variable
------------
Eyal Peleg at 9/25/00 4:11:27 PM
Hi,
I know I have exec to do things like :
DECLARE @str varchar(3000)
DECLARE @tbl varchar(128)
SET @tbl = 'myTable' -- assume I get that info from somewhere
SET @str = 'SELECT * FROM ' + ltrim(@tbl)
EXEC @str
BUT : what if I want to do something like : select @myvar = field1 from @tbl ?
I cannot pass @myvar to the exec - other scope.
I cannot write it just plain ,
Any idea ?
eyal