I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable.
I know I can use sp_executesql
but can\'t find clear examples around about how to do this.
I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable.
I know I can use sp_executesql
but can\'t find clear examples around about how to do this.
If you have OUTPUT parameters you can do
DECLARE @retval int
DECLARE @sSQL nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @tablename nvarchar(50)
SELECT @tablename = N\'products\'
SELECT @sSQL = N\'SELECT @retvalOUT = MAX(ID) FROM \' + @tablename;
SET @ParmDefinition = N\'@retvalOUT int OUTPUT\';
EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;
SELECT @retval;
But if you don\'t, and can not modify the SP:
-- Assuming that your SP return 1 value
create table #temptable (ID int null)
insert into #temptable exec mysp \'Value1\', \'Value2\'
select * from #temptable
Not pretty, but works.
DECLARE @tab AS TABLE (col VARCHAR(10), colu2 varchar(10))
INSERT into @tab EXECUTE sp_executesql N\'SELECT 1 as col1, 2 as col2 union all SELECT 1 as col1, 2 as col2 union all SELECT 1 as col1, 2 as col2\'
SELECT * FROM @tab
DECLARE @vi INT
DECLARE @vQuery VARCHAR(1000)
SET @vQuery = \'SELECT @vi= COUNT(*) FROM <TableName>\'
EXEC SP_EXECUTESQL
@Query = @vQuery
, @Params = N\'@vi INT OUTPUT\'
, @vi = @vi OUTPUT
SELECT @vi
Declare @variable int
Exec @variable = proc_name
Return values are generally not used to \"return\" a result but to return success (0) or an error number (1-65K). The above all seem to indicate that sp_executesql does not return a value, which is not correct. sp_executesql will return 0 for success and any other number for failure.
In the below, @i will return 2727
DECLARE @s NVARCHAR(500)
DECLARE @i INT;
SET @s = \'USE [Blah]; UPDATE STATISTICS [dbo].[TableName] [NonExistantStatisticsName];\';
EXEC @i = sys.sp_executesql @s
SELECT @i AS \'Blah\'
SSMS will show this Msg 2727, Level 11, State 1, Line 1 Cannot find index \'NonExistantStaticsName\'.
If you want to return more than 1 value use this:
DECLARE @sqlstatement2 NVARCHAR(MAX);
DECLARE @retText NVARCHAR(MAX);
DECLARE @ParmDefinition NVARCHAR(MAX);
DECLARE @retIndex INT = 0;
SELECT @sqlstatement = \'SELECT @retIndexOUT=column1 @retTextOUT=column2 FROM XXX WHERE bla bla\';
SET @ParmDefinition = N\'@retIndexOUT INT OUTPUT, @retTextOUT NVARCHAR(MAX) OUTPUT\';
exec sp_executesql @sqlstatement, @ParmDefinition, @retIndexOUT=@retIndex OUTPUT, @retTextOUT=@retText OUTPUT;
returned values are in @retIndex and @retText
DECLARE @ValueTable TABLE ( Value VARCHAR (100) )
SELECT @sql = N\'SELECT SRS_SizeSetDetails.\'+@COLUMN_NAME+\' FROM SRS_SizeSetDetails WHERE FSizeID = \'\'\'+@FSizeID+\'\'\' AND SRS_SizeSetID = \'\'\'+@SRS_SizeSetID+\'\'\'\';
INSERT INTO @ValueTable
EXEC sp_executesql @sql;
SET @Value=\'\';
SET @Value = (SELECT TOP 1 Value FROM @ValueTable)
DELETE FROM @ValueTable
This was a long time ago, so not sure if this is still needed, but you could use @@ROWCOUNT variable to see how many rows were affected with the previous sql statement.
This is helpful when for example you construct a dynamic Update statement and run it with exec. @@ROWCOUNT would show how many rows were updated.
Here is the definition
This worked for me:
DECLARE @SQL NVARCHAR(4000)
DECLARE @tbl Table (
Id int,
Account varchar(50),
Amount int
)
-- Lots of code to Create my dynamic sql statement
insert into @tbl EXEC sp_executesql @SQL
select * from @tbl