SQL 变量作为 Where 子句中的列名 [英] SQL Variables as Column names in Where Clause

问题描述

我的 SQL 逻辑需要一些帮助，我已经为此工作(和研究)了 2 天，但成功率为零.

I need some help with my SQL logic, and I've been working (and researching) this for 2 days now with zero success.

我的目标是尝试将变量从 ASP 页传递到存储过程，该过程将该变量用作 where 子句中列名的条件.

My goal is to try an pass a variable from an ASP page to a stored procedure, which is utilizing the variable as criteria for a column name in the where clause.

例如(我的查询的简化版本):

So for example (a simplified version of my query):

@strDept nvarchar(10), @strUser nvarchar(30)
-- The asp page will pass f18 to @strDept & Ted Lee to strUser
-- f18 is the column name in my database that I need in the where.

select x, y, z from table1 where @strDept in (@strUser)
-- and this is the select statement, notice the where clause.

存储过程确实执行，但它不返回任何值，我知道它将@strDept 视为文字 nvarchar 而不是列名.

The stored procedure does execute, but it returns no values and I know its treating the @strDept as a literal nvarchar and not a column name.

所以我想我的问题是，如何让 SQL Server 2005 将我的 @sqlDept 变量视为列名?

So I guess my question is, how do I get SQL Server 2005 to treat my @sqlDept variable as a column name?

推荐答案

如果这是一个公司内部的应用程序，为什么每个人都在反复迭代 SQL Injection 并将其打败…… 使用 Dynamic SQL 非常简单.如果您对这些只是使用它的内部用户感到满意，那么它非常简单.这是概念.您本质上是编写一个 SQL 语句，该语句写入一个真正是 SQL 语句的字符串，然后执行它.

If this is an internal company application why is everyone re-iterating and beating SQL Injection to death... Its very simple to just use Dynamic SQL. If you are comfortable that these are only internal users using this then its very simple. Here is the concept. You essentially write a SQL Statement that writes a string that is really a SQL statement and then execute it.

CREATE Procedure myDynamicProcedure
@strDept nvarchar(10), 
@strUser nvarchar(30)

as 

BEGIN

1.声明一个变量来存储 SQL 语句.

 DECLARE @SQL varchar(max)

2.将您的@SQL 变量设置为 SELECT 语句.基本上，您正在构建它，以便它返回您想要编写的内容.像这样:

   SET @SQL = 'select x, y, z from table1 where' + @strDept + 
 ' in ' + @strUser

3.执行@SQL 语句，它将与您运行的完全一样:SELECT x,y,z from table1 where f18 = 'Ted Lee'

3. Execute the @SQL Statement and it will be exactly like you ran: SELECT x,y,z from table1 where f18 = 'Ted Lee'

EXEC (@SQL)
END

这篇关于SQL 变量作为 Where 子句中的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！