将变量传递给存储过程SELECT命令[duplicate](Pass a variable into a stored procedures SELECT command [duplicate])

将变量传递给存储过程SELECT命令[duplicate](Pass a variable into a stored procedures SELECT command [duplicate])

这个问题在这里已有答案：

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 answers

I 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 END

basically 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 END

You 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