过程中运行时的动态列名称 [英] dynamic column name at runtime in procedure
问题描述
你好......
我需要在报告生成器中创建报告。 Sql Query对于每个报告都是相同的,但是表的列名更改报告到报告,对于我创建的过程如下
< span class =code-keyword> alter proc sp_testdynamiccolname
(
@ columnname nvarchar ( 100 ),
@date nvarchar ( 10 )
)
< span class =code-keyword> as
begin
声明 @ query nvarchar ( 4000 )
set @ query = ' 选择最大值( ' + @ columnname + ' )as maxval,datepart(hour,timestamp)as pointdb from pointdb where convert(varchar,timestamp,101)in(' + Convert( varchar , @ date , 101 )+ ' )group by datepart(小时,时间戳)'
exec ( @ query )
end
exec sp_testdynamiccolname'SOLAR_ACTIVE_POWER_G_VAL0','03/07/2014'
但它显示错误: -
消息245,级别16,状态1,行1
将varchar值'03 / 04/2014'转换为数据类型时转换失败int。
plz建议解决方案。
你肯定在这里缺少报价。您可以在存储过程中使用 print @query ,以了解 @query 的外观。它看起来像这样:
选择 max(SOLAR_ACTIVE_POWER_G_VAL0) as maxval,datepart(小时, timestamp ) 小时来自 pointdb 其中 convert ( varchar , timestamp , 101 ) in (03/07/2014) group by datepart(小时) , timestamp )
为了解决此错误,@ date附近的代码应如下所示:
(' < span class =code-string>''+ Convert(varchar,@ date,101)+''')
< b>可选信息:
还有另一种编写动态SQL的方法。它可能对你有用。请查看以下链接:
1. sp_executesql(Transact-SQL) [ ^ ]
2. EXEC和sp_executesql - 他们是怎么回事不同? [ ^ ]
hello there......
i need to create reports in report builder. Sql Query is same for every report but the column name of table changes report to report, For That i created procedure as follows
alter proc sp_testdynamiccolname
(
@columnname nvarchar(100),
@date nvarchar(10)
)
as
begin
declare @query nvarchar(4000)
set @query='Select max('+@columnname+') as maxval,datepart(hour,timestamp) as hour from pointdb where convert(varchar,timestamp,101) in('+Convert(varchar,@date,101)+') group by datepart(hour,timestamp)'
exec(@query)
end
exec sp_testdynamiccolname 'SOLAR_ACTIVE_POWER_G_VAL0','03/07/2014'
but it shows an error:-
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '03/04/2014' to data type int.
plz suggest solution.
Hi,
You're definitely missing quotes here. You could use print @query in your stored procedure in order to see how @query looks like. It looks like this:
Select max(SOLAR_ACTIVE_POWER_G_VAL0) as maxval,datepart(hour,timestamp) as hour from pointdb where convert(varchar,timestamp,101) in(03/07/2014) group by datepart(hour,timestamp)
In order to fix this error, your code near @date should be like this:
in('''+Convert(varchar,@date,101)+''')
Optional information:
There is another way to write dynamic SQL. It might be useful to you. Please take a look at these links:
1. sp_executesql (Transact-SQL)[^]
2. EXEC and sp_executesql – how are they different?[^]
这篇关于过程中运行时的动态列名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!