变量存储过程中的Where子句

I am trying to use a stored procedure that has many possible where clauses. What I have for the stored procedure now returns "Incorrect syntax near the keyword 'from'" as an error.

我试图使用具有许多可能where子句的存储过程。我现有的存储过程现在返回“关键字'附近的语法不正确'作为错误。

Select @SQL = 'SELECT Table1.Col1, Table2.Col2 
FROM Table1
INNER JOIN Table2 on Table1.Col1 = Table2.Col1
WHERE ' + @where

Exec(@SQL)

@Where would something like

@Where会是这样的

'Table1.Col1 = 'Apples' OR Table1.Col1 = 'BANNANAS' OR Table2.Col2 = 'CHOCOLATE''

Edit: After messing around with it with all the suggestions I was able to get it to run without an error. Now it wont return any results though.

编辑:在用所有建议搞砸了之后我能够让它运行而没有错误。现在它不会返回任何结果。

4 个解决方案

#1

Could your issue be that you need to double quote all of your literals?

您的问题可能是您需要加倍引用所有文字吗?

'Table1.Col1 = ''Apples'' OR Table1.Col1 = ''BANNANAS'' OR Table2.Col2 = ''CHOCOLATE'''

#2

Use char(39) as quotes.

使用char(39)作为引号。

'Table1.Col1 = '+char(39)+Apples+char(39)+' OR Table1.Col1 = '+ Char(39)+BANNANAS+char(39)+' OR Table2.Col2 = '+char(39)+CHOCOLATE+char(39)'

#3

Here is an alternative solution.

这是另一种解决方案。

It's not "dynamic sql", but it is flexible and "cleaner" IMHO.

它不是“动态sql”,但它是灵活的,“更清洁”恕我直言。

Use Northwind
GO

declare @holder table (ProductName nvarchar(40))


Insert into @holder (ProductName )
select
'Chai' union all select 'Chang' union all select 'Aniseed Syrup'

SELECT 
        [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      ,[UnitsInStock]
      ,[UnitsOnOrder]
      ,[ReorderLevel]
      ,[Discontinued]
FROM 
  [dbo].[Products] prod
  where
        exists (select null from @holder innerH where innerH.ProductName = prod.ProductName)


declare @holderCOUNT int
select @holderCOUNT = count(*) from @holder


SELECT 
        [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      ,[UnitsInStock]
      ,[UnitsOnOrder]
      ,[ReorderLevel]
      ,[Discontinued]
FROM 
  [dbo].[Products] prod
  where
    ( @holderCOUNT = 0 OR ( exists (select null from @holder innerH where innerH.ProductName = prod.ProductName)  ))

delete from @holder
select @holderCOUNT = count(*) from @holder

SELECT 
        [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      ,[UnitsInStock]
      ,[UnitsOnOrder]
      ,[ReorderLevel]
      ,[Discontinued]
FROM 
  [dbo].[Products] prod
  where
    ( @holderCOUNT = 0 OR ( exists (select null from @holder innerH where innerH.ProductName = `enter code here`prod.ProductName)  ))

#4

-1

Just guessing, but should you use a different name for the variable "@Where".

只是猜测,但你应该为变量“@Where”使用不同的名称。

Btw) it's not a good practice to make such kind of building queries from strings. Performance, security and all these kind of problems.

顺便说一句,从字符串中进行这种构建查询并不是一个好习惯。性能,安全性和所有这些问题。

André

#1