我需要在存储过程的 select 语句中使用变量传递列名，但我不能使用动态查询
下面是我的 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.
相关文章