store result of query into a variable

[KritiGuleria]

i need to put the result of quey @st into a variable. could anyone please tell me how to do that?


select @sys= + columnName+', ' from tab
set @sys=left(@sys, len(@sys)-1)
select @sys

DECLARE cursor_insert CURSOR FOR
SELECT insequenceid FROM temp_ac
OPEN cursor_insert
FETCH NEXT FROM cursor_Insert into @inseq

WHILE @@FETCH_STATUS = 0
BEGIN
set @st= ('select ' + @sys + ' from temp_ac ')
execute (@st)
FETCH NEXT FROM cursor_Insert into @inseq
END
CLOSE cursor_insert
DEALLOCATE cursor_insert

i want something so that @val= execute(@st)?????

[davef]

Somethig like this will do:

declare @sql varchar(100),
@szDesc varchar(50)

--Create a test table and dump some data into it
create table Account (
nID int NOT NULL PRIMARY KEY,
szDesc varchar(50)
)
insert into Account
select 1, 'Primary'
insert into Account
select 2, 'Secondary'
insert into Account
select 3, 'Offshore'

--Declare a table variable corresponding in structure to Account table
declare @temp table (
nID int NOT NULL PRIMARY KEY,
szDesc varchar(50)
)
set @sql='select * from Account'
insert into @temp
exec (@sql)

--Store result of dynamic SQL query in a variable
select @szDesc=szDesc from @temp where szDesc like 'P%'
print @szDesc

--Drop the test table
drop table Account

[KritiGuleria]

this is not working.
i'm getting the error
"EXECUTE cannot be used as a source when inserting into a table variable"

moreover i cannot use temporary table as i dont kno the exact number of columns that are to be fetched as those columns will be selected on runtime only.

[azimmer]

i need to put the result of quey @st into a variable. could anyone please tell me how to do that?


select @sys= + columnName+', ' from tab
set @sys=left(@sys, len(@sys)-1)
select @sys

DECLARE cursor_insert CURSOR FOR
SELECT insequenceid FROM temp_ac
OPEN cursor_insert
FETCH NEXT FROM cursor_Insert into @inseq

WHILE @@FETCH_STATUS = 0
BEGIN
set @st= ('select ' + @sys + ' from temp_ac ')
execute (@st)
FETCH NEXT FROM cursor_Insert into @inseq
END
CLOSE cursor_insert
DEALLOCATE cursor_insert

i want something so that @val= execute(@st)?????

With EXEC you open a new environment: that is you cannot directly move data in and out of the EXEC scope. If you can process @val within the EXEC scope (that is: it is not utterly complex or a return value) I suggest you define and process it within @st like this:
change this:
set @st= ('select ' + @sys + ' from temp_ac ')
to this:
set @st= ('select @val=' + @sys + ' from temp_ac ')
(append to your code):

set @st='declare @val varchar(255); '+@st
set @st=@st+'; print @val;'    ' or whatever your processing is
 

now EXEC(@st)

There can be tricky workarounds to this but if it works look no further.