select @var = f1 from @tbl

[Eyal Peleg]

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]

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

[Eyal Peleg]

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]

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