来自字符串的 SQL 查询

Question

我正在尝试从我使用变量生成的字符串执行 SQL 查询。这个 SP 生成一个 Select 查询，当我手动执行这个查询时它可以工作，但是当它在 SP 中时会出现类似

的错误

Msg 911, Level 16, State 4, Procedure sp_Products_Select, Line 26
Database 'SELECT *,(Select Section From Sections Where SectionID = Products' does not exist. Make sure that the name is entered correctly.

从 SP 生成的查询

SELECT 
   *, 
   (Select Section 
    From Sections 
    Where SectionID = dbo.Products.SectionID) as Section,
   (Select Category 
    From Categories 
    Where CategoryID = Products.CategoryID) as Category 
FROM 
    Products 
WHERE 
    1 = 1AND (Status = 1 OR Status = 0)

我的SP是，

ALTER PROC [dbo].[sp_Products_Select]
    @ProductID int,
    @Status int,
    @CategoryID int,
    @SectionID int
AS

DECLARE @SQL varchar(500)
SET @SQL ='SELECT *,(Select Section From Sections Where SectionID = Products.SectionID) as Section,(Select Category From Categories Where CategoryID = Products.CategoryID) as Category FROM Products WHERE 1 = 1';
IF @ProductID <> '0' BEGIN
    SET @SQL += 'AND ProductID = @ProductID';
END
IF  @Status <> 0 BEGIN 
    SET @SQL += 'AND Status = @Status';
END ELSE BEGIN
    SET @SQL += 'AND (Status = 1 OR Status = 0)';
END
IF @CategoryID <> '0' BEGIN
    SET @SQL += 'AND CategoryID = @CategoryID';
END
IF @SectionID <> '0' BEGIN
    SET @SQL += 'AND SectionID = @SectionID';
END

EXEC @SQL

如何从 SP 正确执行此查询？

Answer 1

您的 WHERE 中缺少一个空格子句:

WHERE 
    1 = 1AND (Status = 1 OR Status = 0)

必须是:

WHERE 
    1 = 1 AND (Status = 1 OR Status = 0)
         *
         * space here is absolutely essential!

因此您需要更改构建 T-SQL 语句的存储过程代码，以便在 1 = 1 之间至少放置一个 一个空格和 AND ....

( 这不是问题所在，正如 Martin Smith 发现的......)

更新: 好的 - 这样看来毕竟不需要空间......

但是:尝试使用

EXEC (@SQL)

我相信您在执行 SQL 语句时需要将其放在括号中。