我可以在 SQL 存储过程中将列名作为输入参数传递吗 [英] Can I pass column name as input parameter in SQL stored Procedure

问题描述

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.

所以帮帮我

推荐答案

您可以通过多种方式做到这一点.

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

如果您选择这种方法，请务必对您的输入进行清理.即使您知道您的应用程序只会给出真实"的列名，但如果有人发现您的安全漏洞并且能够直接执行 SP 怎么办?然后他们可以执行他们喜欢的任何事情.使用动态 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.

或者，您可以编写 CASE 语句...

Alternatively, you can write a CASE statement...

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.

这篇关于我可以在 SQL 存储过程中将列名作为输入参数传递吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！