CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
PJD
PJD

Reputation: 775

select statement in a variable I need to out put to a temp table

I am sure this is easy but I have a select statement stored as a variable e.g. @statement contains a statement "select count(*) from table1" I need to execute this variable e.g. sp_executesql @statement but put the results in to a temp table, is this possible easily?

Thanks

Upvotes: 0

Views: 48

Answers (3)

PJD
PJD

Reputation: 775

Sorry to say I wasn't given all of the question, where in the select statement I posted it only returned 1 field but I have now been told that the count i.e. 1 field was just an example and I might need to return multiple fields so SqlZim response would not of worked I don't think as I wouldn't always know the number/name of columns. What I chose to do was the following :-

Declare @TempSQLStatement nvarchar(max)

SET @TempSQLStatement=REPLACE(@statement,' FROM ',' INTO ##temp FROM ');
EXEC sp_executesql @TempSQLStatement; 

SELECT * from ##temp

Not sure how to award the answer as Alex's solution would work fine and I believe SqlZim would of posted something similar if I had posted the complete info in the first place, can admin assist?

Upvotes: 0

Alex K.
Alex K.

Reputation: 175796

One way:

declare @statement nvarchar(max) = 'select count(*) from table1'

declare @sql nvarchar(max) = N'set @result = (' + @statement + ')'

declare @result int
exec sp_executesql @sql, N'@result int OUTPUT', @result=@result OUTPUT;

select @result;

select @result as count into #temp

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

You can create your temp table first, then use insert into ... exec...:

declare @statement nvarchar(max);
set @statement = 'select 1';

create table #temp (rc int);
insert into #temp (rc)
exec sp_executesql @statement;

select * from #temp;

rextester demo: http://rextester.com/WPDAZ22362

returns: 1

Upvotes: 2

Related Questions