没有动态查询的列名变量 [英] Column name variable without dynamic query

问题描述

大家好，
 
以下查询不返回任何行，但DB有值。任何人都可以在不使用动态查询的情况下提供帮助
 DECLARE @ Search1 NVARCHAR（200）
 DECLARE @ Search2 NVARCHAR（200）
 SET @ Search1 ='1673437'
 SET @ Search2 = 'SSN'
 SELECT TOP 10 ID，FirstName，LastName，Age FROM Persons WHERE @ Search2 = @ Search1 
 
 
 
问候，
 
 stellus。

我尝试过：

没有动态查询的列名变量

解决方案

这正是动态查询的类型用于。

  DECLARE   @ Search1   NVARCHAR （ 200 ）; 
  DECLARE   @ Search2   NVARCHAR  （ 200 ）; 
 
  SET   @ Search1  =  '  1673437'; 
  SET   @ Search2  = '  SSN'; 
 
 
   -   绝不相信用户输入！ 
  DECLARE   @ SearchColumn  sysname; 
 
  SELECT  
  @ SearchColumn  = QUOTENAME（name）
  FROM  
 sys.columns 
  WHERE  
 name = < span class =code-sdkkeyword> @ Search2  
 和 
 object_id =  OBJECT_ID （N ' 人员）
; 
 
  DECLARE   @ command   nvarchar （最大）; 
  SET   @ command  = N '  SELECT TOP 10 ID，FirstName，LastName，Age FROM Persons WHERE' +  @ SearchColumn  + N < span class =code-string>'  = @ Search1'; 
  EXEC  sp_executesql  @ command ，N '  @ Search1 nvarchar（200）'， @ Search1  =  @ Search1 ; 

sp_executesql（Transact-SQL）| Microsoft Docs [ ^ ]



如果由于某些奇怪的原因你不能或不想使用动态查询，那么你唯一的选择是对每个可能的列进行单独检查：

  SELECT   TOP   10  
 ID，
 FirstName，
 LastName，
年龄
  FROM  
人员
  WHERE  
（ @ Search2  = N '  SSN' 和 SSN =  @ Search1 ）
 或 
（ @ Search2  = N '  FirstName' 和 FirstName =  @ Search1 ）
 或 
 ... 

 SELECT TOP 10 ID，FirstName，LastName，Age，@ Search1 AS Col1，@ Search2 AS Col2 
 FROM Persons 
 WHERE @ Search1 = @ Search1并且@ Search2 = @ Search2 

Hi All,

Below query is not returning any row but DB has the value. Can anyone help on this without using dynamic query
DECLARE @Search1 NVARCHAR(200)                                  
 DECLARE @Search2 NVARCHAR(200)
 SET @Search1 = '1673437'
 SET @Search2 = 'SSN'
 SELECT TOP 10 ID, FirstName, LastName, Age FROM Persons WHERE  @Search2 = @Search1



with regards,

stellus.

What I have tried:

Column name variable without dynamic query 

解决方案

That's precisely the sort of thing dynamic queries are meant for.

DECLARE @Search1 NVARCHAR(200);
DECLARE @Search2 NVARCHAR(200);

SET @Search1 = '1673437';
SET @Search2 = 'SSN';


-- Never trust user input!
DECLARE @SearchColumn sysname;

SELECT 
    @SearchColumn = QUOTENAME(name)
FROM
    sys.columns
WHERE
    name = @Search2
And
    object_id = OBJECT_ID(N'Persons')
;

DECLARE @command nvarchar(max);
SET @command = N'SELECT TOP 10 ID, FirstName, LastName, Age FROM Persons WHERE ' + @SearchColumn + N' = @Search1';
EXEC sp_executesql @command, N'@Search1 nvarchar(200)', @Search1 = @Search1;

sp_executesql (Transact-SQL) | Microsoft Docs[^]

If for some bizarre reason you can't, or don't want to, use a dynamic query, then your only option is a separate check for every possible column:

SELECT TOP 10 
    ID, 
    FirstName, 
    LastName, 
    Age 
FROM 
    Persons 
WHERE 
    (@Search2 = N'SSN' And SSN = @Search1)
Or 
    (@Search2 = N'FirstName' And FirstName = @Search1)
Or 
    ...

SELECT TOP 10 ID, FirstName, LastName, Age,@Search1  AS Col1,@Search2 AS Col2
 FROM Persons
   WHERE  @Search1 = @Search1 And @Search2 = @Search2  

这篇关于没有动态查询的列名变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！