我需要在存储过程的 select 语句中使用变量传递列名，但我不能使用动态查询 [英] I need to pass column names using variable in select statement in Store Procedure but i cannot use dynamic query

问题描述

下面是我的 SQL 查询.我想从作为变量给出的列名中选择值.除了使用动态查询之外，还有其他合适的方法吗?

Here is my SQL query below. I want to select values from the column names given as variables. Is there any appropriate way of doing this except using a dynamic query?

SELECT EPV.EmployeeCode, @RateOfEmployee, @RateOfEmployer
FROM [HR_EmployeeProvisions] EPV

推荐答案

你不能参数化 标识符，我怀疑它在任何其他关系数据库中是否可行.

You can't parameterize identifiers in Sql server, and I doubt it's possible in any other relational database.

最好的选择是使用动态Sql.

Your best choice is to use dynamic Sql.

请注意，动态 sql 通常存在安全隐患，您必须保护您的代码免受sql 注入 攻击.

Note that dynamic sql is very often a security hazard and you must defend your code from sql injection attacks.

我可能会做这样的事情:

I would probably do something like this:

Declare @Sql nvarchar(500)
Declare numberOfColumns int;

select @numberOfColumns = count(1)
from information_schema.columns
where table_name = 'HR_EmployeeProvisions'
and column_name IN(@RateOfEmployee, @RateOfEmployer)

if @numberOfColumns = 2 begin

Select @Sql = 'SELECT EmployeeCode, '+ QUOTENAME(@RateOfEmployee) +' ,'+ QUOTENAME(@RateOfEmployer) +
'FROM HR_EmployeeProvisions'

exec(@Sql)
end

通过这种方式，您可以确保表中确实存在列名，并使用 QUOTENAME 作为另一层安全.

This way you make sure that the column names actually exists in the table, as well as using QUOTENAME as another layer of safety.

注意:在您的表示层中，您应该处理由于列名无效而不会执行选择的选项.

Note: in your presentation layer you should handle the option that the select will not be performed since the column names are invalid.

这篇关于我需要在存储过程的 select 语句中使用变量传递列名，但我不能使用动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！