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
);
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;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments