I'm doing dynamic SQL to convert all columns in a table a string
so After after all I do
EXEC(@template);
where @template is the dynamic generated query so:
col1 col2 col3
---------------
1 7 13
2 8 14
3 9 15
4 10 16
5 11 17
6 12 18
(this results: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18)
How do I assign to a variable the resulting string
something like?
DECLARE @result AS varchar(max);
SET @result = EXEC(@template);
This question is related to
sql-server
string
You can use sp_executesql with output parameter.
declare @S nvarchar(max) = 'select @x = 1'
declare @xx int
set @xx = 0
exec sp_executesql @S, N'@x int out', @xx out
select @xx
Result:
(No column name)
1
Edit
In my sample @S is instead of your @template. As you can see I assign a value to @x so you need to modify @template so it internally assigns the comma separated string to the variable you define in your second argument to sp_executesql. In my sample N'@x int out'. You probably want a varchar(max) output parameter. Something like N'@Result varchar(max) out'
Here is another example building a comma separated string from master..spt_values
declare @template nvarchar(max)
set @template =
'select @Result += cast(number as varchar(10))+'',''
from master..spt_values
where type = ''P''
'
declare @CommaString varchar(max)
set @CommaString = ''
exec sp_executesql @template, N'@Result varchar(max) out', @CommaString out
select @CommaString
You could use sp_executesql instead of exec. That allows you to specify an output parameter.
declare @out_var varchar(max);
execute sp_executesql
N'select @out_var = ''hello world''',
N'@out_var varchar(max) OUTPUT',
@out_var = @out_var output;
select @out_var;
This prints "hello world".
You should try this while getting SEQUENCE value in a variable from the dynamic table.
DECLARE @temp table (#temp varchar (MAX));
DECLARE @SeqID nvarchar(150);
DECLARE @Name varchar(150);
SET @Name = (Select Name from table)
SET @SeqID = 'SELECT NEXT VALUE FOR '+ @Name + '_Sequence'
insert @temp exec (@SeqID)
SET @SeqID = (select * from @temp )
PRINT @SeqID
Result:
(1 row(s) affected)
1
Most of these answers use sp_executesql as the solution to this problem. I have found that there are some limitations when using sp_executesql, which I will not go into, but I wanted to offer an alternative using EXEC(). I am using SQL Server 2008 and I know that some of the objects I am using in this script are not available in earlier versions of SQL Server so be wary.
DECLARE @CountResults TABLE (CountReturned INT)
DECLARE
@SqlStatement VARCHAR(8000) = 'SELECT COUNT(*) FROM table'
, @Count INT
INSERT @CountResults
EXEC(@SqlStatement)
SET @Count = (SELECT CountReturned FROM @CountResults)
SELECT @Count
Sample to execute an SQL string within the stored procedure:
(I'm using this to compare the number of entries on each table as first check for a regression test, within a cursor loop)
select @SqlQuery1 = N'select @CountResult1 = (select isnull(count(*),0) from ' + @DatabaseFirst+'.dbo.'+@ObjectName + ')'
execute sp_executesql @SqlQuery1 , N'@CountResult1 int OUTPUT', @CountResult1 = @CountResult1 output;
Source: Stackoverflow.com