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

Question

I have the following theoretical statement which I would like to implement using dynamic SQL (on SQL Server 2016) and store the single output value in the variable (@output).

@numericvar, @columnname, @tablename should be input parameters. Any help would be greatly appreciated. Many thanks.

SELECT @output = (
    SELECT 
        MAX(CASE WHEN ROWNUM*1.0/NUMROWS <= @numericvar THEN @columnname END)
    FROM (
        SELECT
            @columnname,
            ROW_NUMBER() OVER (ORDER BY @columnname ) AS ROWNUM,
            COUNT(*) OVER (PARTITION BY NULL) AS NUMROWS
        FROM 
            @tablename 
    ) @tablename 
);

TT. · Accepted Answer · 2016-11-06T06:26:11.993

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;

I get an Arithmetic overflow error converting nvarchar to data type numeric. — Kanarinox, Nov 05 '16 at 15:16
@Kanarinox What did you take for the type of variable `@output`? And what is the type of column which you pass in `@columnname`? — TT., Nov 05 '16 at 17:24
@Kanarinox Updated answer. I'm thinking you have some `NVARCHAR()` column in there somewhere which has invalid float values maybe? In that case try a `SELECT _float_column_,TRY_CAST(_float_column_ AS FLOAT) FROM _your_table_` to see wich values are not valid floats. — TT., Nov 06 '16 at 06:27

James · Answer 2 · 2016-11-05T17:52:49.993

try this, i have copy pasted here and there , you might have to work around a bit in case.

declare @query nvarchar(max)
declare @output nvarchar(max)
declare @columnname nvarchar(max)
declare @tablename nvarchar(max)
declare  @numericvar NUMERIC(18,0)

    set @query ='SELECT @output = (
        SELECT 
            MAX(CASE WHEN ROWNUM*1.0/NUMROWS <= @numericvar THEN @columnname END)
        FROM (
            SELECT
                @columnname,
                ROW_NUMBER() OVER (ORDER BY @columnname ) AS ROWNUM,
                COUNT(*) OVER (PARTITION BY NULL) AS NUMROWS
            FROM 
                @tablename 
        ) @tablename 
    );'

    exec sp_executesql @query, N'@output numeric(18,2) output, 
    @columnname nvarchar(max), declare @numericvar NUMERIC(18,0),
    declare @tablename nvarchar(max)', @output= @output output, @columnname= @columnname,  @numericvar=@numericvar, @tablename=@tablename
    select @output

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

2 Answers2