Getting Result of Dynamic SQL into a Variable For Sql-Server

Getting result of dynamic SQL into a variable for sql-server

DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @count INT
DECLARE @city VARCHAR(75)
SET @city = 'New York'

SET @sqlCommand = 'SELECT @cnt=COUNT(*) FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75), @cnt int OUTPUT', @city = @city, @cnt = @count OUTPUT

SELECT @count

Dynamic SQL get one value from custom @Column into result variable

You can't parametrise an object name, you have to safely inject the value.

This should be what you're after:

DECLARE @QuantityColumnName sysname = N'ColumnName'; --Correct datatype for object names

DECLARE @sql NVARCHAR(MAX);        
DECLARE @qty money;
SET @sql = N'SELECT TOP 1 @qty = ' + QUOTENAME(@QuantityColumnName) + N' FROM #temporaryTable WHERE ID =  @Id;'
EXEC sp_executesql @sql, N' ,@Id bigint, @qty money OUTPUT', @Id = @Id, @qty = @qty OUTPUT;

Assign result of dynamic sql to variable

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

How to store the result of dynamic SQL in a variable?

DECLARE @columnname SYSNAME, @tablename SYSNAME, @numericvar NUMERIC(18,2);
DECLARE @output NUMERIC(18,2);

DECLARE @sql NVARCHAR(MAX) = N'
    SET @output = (
        SELECT 
            MAX(CASE WHEN ROWNUM*1.0/NUMROWS <= @numericvar THEN '+QUOTENAME(@columnname)+N' END)
        FROM (
            SELECT
                '+QUOTENAME(@columnname)+N',
                ROW_NUMBER() OVER (ORDER BY '+QUOTENAME(@columnname)+N' ) AS ROWNUM,
                COUNT(*) OVER (PARTITION BY NULL) AS NUMROWS
            FROM 
                '+QUOTENAME(@tablename)+N'
        ) AS t
    );
';

EXECUTE sp_executesql 
    @sql,
    N'@numericvar NUMERIC(18,2), @output NUMERIC(18,2) OUTPUT', 
    @numericvar, @output OUTPUT;

SELECT @output;

---

Update: a working example for FLOAT output. The script uses a table in the INFORMATION_SCHEMA schema which everyone has.

See if you can make it work from this sample. If you can't I suggest you edit your question, and add the exact script + parameter values you are using + indication of the type of columnname.

DECLARE @schemaname SYSNAME='INFORMATION_SCHEMA',
        @tablename  SYSNAME='COLUMNS', 
        @columnname SYSNAME='NUMERIC_PRECISION', 
        @numericvar NUMERIC(18,2)=.5;

DECLARE @output_f FLOAT;

DECLARE @sql NVARCHAR(MAX) = N'
    SET @output_f = (
            SELECT 
                MAX(CASE WHEN ROWNUM*1.0/NUMROWS<=@numericvar THEN '+QUOTENAME(@columnname)+N' END)
            FROM (
                SELECT
                    '+QUOTENAME(@columnname)+N',
                    ROW_NUMBER() OVER (ORDER BY '+QUOTENAME(@columnname)+N') AS ROWNUM,
                    COUNT(*) OVER () AS NUMROWS
                FROM 
                    '+QUOTENAME(@schemaname)+N'.'+QUOTENAME(@tablename)+N'
            ) AS t
    );
';

EXECUTE sp_executesql 
    @sql,
    N'@numericvar NUMERIC(18,2), @output_f FLOAT OUTPUT', 
    @numericvar, @output_f OUTPUT;

SELECT @output_f;

Dynamic SQL output of a query to a variable

You can utilize sp_executesql to execute your count() query, and output it @Count.

Try this:

-- Set the table to count from
declare @tab nvarchar(255) = 'Person.person'

-- Assign the SQL query
declare @SQL nvarchar(255) = N'SELECT count(*) FROM ' + @tab

-- Pepare for sp_executesql
declare @Count int
declare @Params nvarchar(100) = N'@Count int output'

-- Set the count to @Count
exec sp_executesql @SQL, @Params, @Count=@Count output

-- Output @Count
select @Count

One last thing: Person.person looks like you might be trying to reference a person column from a Person table. But the above query is a literal representation of what it looks like you're trying to achieve in your question.

SQL getting value of dynamic query into a variable

You need to assign your @Output variable to your result count.

@Output = COUNT(ServerName)

complete script

DECLARE @Output INT 

SELECT @SqlCommand = 'SELECT @Output = COUNT(ServerName) FROM ' + @TableReference + ' WITH (NOLOCK) WHERE ServerName = ''' + @PackageEndPoint + ''''

EXEC sp_executesql @SqlCommand, N'@Output INT OUTPUT',@Output = @Output OUTPUT
SELECT  @StagingRecordCount = @Output
SELECT @StagingRecordCount

How To Get Value of Variable From Dynamic Query

Here is a small example of how to use dynamic sql. It accepts one variable as input to the dynamic string, and outputs a single value sum(status) into the variable @l_out1

You may modify this example to suit what you require. In you example, i could find you have a statement sum(@value),however @value is not defined.

declare @str       nvarchar(4000)
declare @l_out1    int
declare @l_status1 int =1


set @str='select @l_out=sum(status) ' 
         + 'from master..spt_values '
         + 'where status=@l_status '

exec sp_executesql @str
                   ,N'@l_status INT,@l_out INT OUTPUT'
                   ,@l_status=@l_status1
                   ,@l_out=@l_out1 output

select @l_out1 as 'col1'

Working example

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4ebab535a4970f478ce22547e5dd8150

SQL Server - Assign value to variable inside Dynamic SQL

Just try this:

DECLARE @result INT
       ,@query NVARCHAR(MAX);

SET @query = 'IF (1 = 0)
BEGIN
  --Some action goes here
  SET @result= 1
END
ELSE
BEGIN
  SET @result= 2
END';

EXEC sp_executesql @query, N'@result INT OUTPUT',@result =  @result OUTPUT

SELECT @result;

You can use sp_executesql in order to execute dynamic T-SQL statement and initialize SQL variables. In the sp_executesql you need to pass the parameter definition and then parameter mappings.

---

Related Topics