如何将动态SQL的结果存储在变量中?
问题描述:
我有以下理论声明,我想使用动态SQL(在SQL Server 2016上)实现并将单个输出值存储在变量(@output
)中。如何将动态SQL的结果存储在变量中?
@numericvar
,@columnname
,@tablename
应该是输入参数。任何帮助将不胜感激。非常感谢。
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;
更新:为FLOAT
输出工作的例子。该脚本使用每个人都有的INFORMATION_SCHEMA
架构中的表格。
看看你是否可以使它从这个样本工作。如果你不能,我建议你编辑你的问题,并添加你正在使用的确切的脚本+参数值+指示的列名类型。
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<[email protected] 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;
答
试试这个,我把复制粘贴在这里和那里,你可能需要解决一下以防万一。
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, @[email protected], @[email protected]
select @output
我得到一个算术溢出错误,将nvarchar转换为数据类型数字。 – Kanarinox
@Kanarinox你对变量'@ output'的类型做了什么?你在'@ columnname'中传递的列的类型是什么? –
浮球和浮球。 (请原谅反应迟滞,我是gmt + 4)。 – Kanarinox