这个问题在这里已有答案:
SQL:根据变量 2的答案 选择动态列名我有一个带有存储过程链接的网页,我想将一个变量传递给存储过程select语句。 到目前为止的代码是 -
ALTER procedure [dbo].[RTO] @Weeknumber int, @asset nvarchar(50) AS Begin SELECT @asset FROM RTO_weeklyanalysis Where weekNumber = @weeknumber END基本上@asset将是列的名称,但这将根据用户在页面上选择的内容而改变。
This question already has an answer here:
SQL: Select dynamic column name based on variable 2 answersI have a web page with a link to a stored procedure and I want to pass a variable to the stored procedures select statement. The code so far is -
ALTER procedure [dbo].[RTO] @Weeknumber int, @asset nvarchar(50) AS Begin SELECT @asset FROM RTO_weeklyanalysis Where weekNumber = @weeknumber ENDbasically the @asset will be the name of the column but this will change depending on what the user selects on the page.
最满意答案
您需要使用Dynamic sql。 在将对象名称连接到sql查询时,也使用QuoteName()函数。 并使用系统存储过程sp_executesql来执行动态查询,以最安全,最安全的方式执行动态sql。 如下:
ALTER procedure [dbo].[RTO] @Weeknumber int, @asset sysname AS Begin SET NOCOUNT ON; DECLARE @Sql NVARCHAR(MAX); SET @Sql = N' SELECT '+ QUOTENAME(@asset) + ' FROM RTO_weeklyanalysis Where weekNumber = @weeknumber' EXECUTE sp_executesql @Sql ,N'@Weeknumber int' ,@Weeknumber ENDYou will need to use Dynamic sql for this. Also use QuoteName() function when concatenating object names to your sql query. and use system stored procedure sp_executesql to execute the dynamic query the most safe and secure way of executing dynamic sql. Something as follows :
ALTER procedure [dbo].[RTO] @Weeknumber int, @asset sysname AS Begin SET NOCOUNT ON; DECLARE @Sql NVARCHAR(MAX); SET @Sql = N' SELECT '+ QUOTENAME(@asset) + ' FROM RTO_weeklyanalysis Where weekNumber = @weeknumber' EXECUTE sp_executesql @Sql ,N'@Weeknumber int' ,@Weeknumber END更多推荐
发布评论