我可以在 SQL 存储过程中将列名作为输入参数传递吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10092869/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I pass column name as input parameter in SQL stored Procedure
提问by user1169809
create procedure sp_First
@columnname varchar
AS
begin
select @columnname from Table_1
end
exec sp_First 'sname'
My requirement is to pass column names as input parameters. I tried like that but it gave wrong output.
我的要求是将列名作为输入参数传递。我试过这样，但它给出了错误的输出。
So Help me
所以帮帮我
回答by MatBailie
You can do this in a couple of ways.
您可以通过多种方式执行此操作。
One, is to build up the query yourself and execute it.
一是自己建立查询并执行它。
SET @sql = 'SELECT ' + @columnName + ' FROM yourTable'
sp_executesql @sql
If you opt for that method, be very certain to santise your input. Even if you know your application will only give 'real' column names, what if some-one finds a crack in your security and is able to execute the SP directly? Then they can execute just about anything they like. With dynamic SQL, always, always, validate the parameters.
如果您选择这种方法，请务必对您的输入进行清理。即使您知道您的应用程序只会给出“真实”的列名，但如果有人发现您的安全漏洞并且能够直接执行 SP，该怎么办？然后他们可以执行他们喜欢的任何事情。使用动态 SQL，始终，始终，验证参数。
Alternatively, you can write a CASE statement...
或者，您可以编写 CASE 语句...
SELECT
CASE @columnName
WHEN 'Col1' THEN Col1
WHEN 'Col2' THEN Col2
ELSE NULL
END as selectedColumn
FROM
yourTable
This is a bit more long winded, but a whole lot more secure.
这有点冗长，但更安全。
回答by Darren Kopp
No. That would just select the parameter value. You would need to use dynamic sql.
不，那只会选择参数值。您将需要使用动态 sql。
In your procedure you would have the following:
在您的程序中，您将拥有以下内容：
DECLARE @sql nvarchar(max) = 'SELECT ' + @columnname + ' FROM Table_1';
exec sp_executesql @sql, N''
回答by John Dewey
Try using dynamic SQL:
尝试使用动态 SQL：
create procedure sp_First @columnname varchar
AS
begin
declare @sql nvarchar(4000);
set @sql='select ['+@columnname+'] from Table_1';
exec sp_executesql @sql
end
go
exec sp_First 'sname'
go
回答by usr
This is not possible. Either use dynamic SQL (dangerous) or a gigantic case expression (slow).
这不可能。使用动态 SQL（危险）或巨大的 case 表达式（慢）。
回答by Jon Raynor
You can pass the column name but you cannot use it in a sql statemnt like
您可以传递列名，但不能在像这样的 sql 语句中使用它
Select @Columnname From Table
One could build a dynamic sql string and execute it like EXEC (@SQL)
可以构建一个动态的 sql 字符串并像这样执行它 EXEC (@SQL)
For more information see this answer on dynamic sql.
有关更多信息，请参阅有关动态 sql 的此答案。
回答by Out
Create PROCEDURE USP_S_NameAvilability
(@Value VARCHAR(50)=null,
@TableName VARCHAR(50)=null,
@ColumnName VARCHAR(50)=null)
AS
BEGIN
DECLARE @cmd AS NVARCHAR(max)
SET @Value = ''''+@Value+ ''''
SET @cmd = N'SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + @Value
EXEC(@cmd)
END
As i have tried one the answer, it is getting executed successfully but while running its not giving correct output, the above works well
正如我已经尝试过一个答案，它已成功执行，但在运行时没有给出正确的输出，上面的效果很好
回答by Saronyo
As mentioned by MatBailie This is much more safe since it is not a dynamic query and ther are lesser chances of sql injection . I Added one situation where you even want the where clause to be dynamic . XX YY are Columns names
正如 MatBailie 所提到的，这更安全，因为它不是动态查询，而且 sql 注入的机会更小。我添加了一种情况，您甚至希望 where 子句是动态的。XX YY 是列名
CREATE PROCEDURE [dbo].[DASH_getTP_under_TP]
(
@fromColumnName varchar(10) ,
@toColumnName varchar(10) ,
@ID varchar(10)
)
as
begin
-- this is the column required for where clause
declare @colname varchar(50)
set @colname=case @fromUserType
when 'XX' then 'XX'
when 'YY' then 'YY'
end
select SelectedColumnId from (
select
case @toColumnName
when 'XX' then tablename.XX
when 'YY' then tablename.YY
end as SelectedColumnId,
From tablename
where
(case @fromUserType
when 'XX' then XX
when 'YY' then YY
end)= ISNULL(@ID , @colname)
) as tbl1 group by SelectedColumnId
end
回答by Sujay
Please Try with this. I hope it will work for you.
请试试这个。我希望它对你有用。
Create Procedure Test
(
@Table VARCHAR(500),
@Column VARCHAR(100),
@Value VARCHAR(300)
)
AS
BEGIN
DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT * FROM ' + @Table + ' WHERE ' + @Column + ' = ' + @Value
--SELECT @sql
exec (@sql)
END
-----execution----
/** Exec Test Products,IsDeposit,1 **/
