我需要在Store Procedure中的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

推荐答案

参数化在Sql服务器中的标识符，我怀疑它可能在任何其他关系数据库。

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.

我可能会这样做：

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.

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