将列名称存储在变量中，并在SQL Server中的查询中使用它

Question

我试图从用户那里获取2个输入，列名和搜索关键字，并筛选他们输入的特定列中的数据。为此，我写了这个存储过程：

Create proc spGetReference 
    @Search nvarchar(255)=Null, --- search Key word 
    @Column nvarchar(200)='MainCode' --- Column Name 
as 
begin 
    With CTE_Table as 
    (
     Select 
      ROW_NUMBER() over(order by L.MainCode) as RowNum, 
      Count(*) over() as TotalRow, 
      ReferenceNo, 
      MainCode, 
      Name, 
      SettlementStatus, 
      DocumentType 
     FROM 
      LcDocArrived L (NOLOCK), 
      Master M (NOLOCK) 
     WHERE 
      ArrStatus = 'P' 
      AND M.MainCode = L.MainCode 
      AND M.BranchCode = L.BranchCode 
      AND M.BranchCode = '001' 
    ) 
    SELECT * 
    FROM CTE_Table 
    WHERE @Search IS NULL 
     OR @Column LIKE '%'[email protected]+'%' 
end 

exec spGetReference 'Ram','Name' 

这没有显示任何记录..我认为列名不能使用像这样。

如何获取列名称并将其用于过滤数据？

Answer 1

你应该用动态的方式写出来。

 CREATE PROC spGetReference @Search NVARCHAR(255) = NULL 
     ,--- search Key word 
     @Column NVARCHAR(200) = 'MainCode' --- Column Name 
    AS 
    BEGIN 
    DECLARE @sql NVARCHAR(max) = '' 

    SET @sql = ' 
;With CTE_Table as 
(
    Select ROW_NUMBER() over(order by L.MainCode) as RowNum, 
    Count(*) over() as TotalRow, 
    ReferenceNo, 
    MainCode, 
    Name, 
    SettlementStatus, 
    DocumentType 
    FROM LcDocArrived L (NOLOCK) 
    INNER JOIN 
    Master M (NOLOCK) ON M.MainCode = L.MainCode 
    and M.BranchCode = L.BranchCode 
    WHERE ArrStatus = ''P'' 
    AND M.BranchCode = ''001'' 
) 
Select * from CTE_Table where ' + @Search + ' Is null or ' + @Column + ' like ''%' + @Search + '%'' 
' 

    EXEC SP_EXECUTESQL @SQL 
END 

EXEC spGetReference 'Ram' 
    ,'Name'