无法执行 SQL Server 存储过程 [英] Can not execute SQL Server stored procedure

问题描述

我有以下过程，我向其中传递了一个字符串参数，例如 ,2,3,.该过程应返回一组行，其中 CategoryId 等于 2 或 3:

I have the following procedure into which I pass a string parameter, for example ,2,3,. The procedure should return a set of rows where CategoryId is equal to 2 or 3:

CREATE PROCEDURE [dbo].[GetItemListProcedure]
    (@CategoriesIdString VARCHAR(1000))
AS
    SET NOCOUNT ON;

    DECLARE @SQLQuery NVARCHAR(MAX);

    SET @SQLQuery = N'SELECT Item.Id, Item.ModelId, Item.ItemCode, Item.CategoryId, 
            (SELECT TOP (1) Category.CategoryName FROM Category WHERE Category.Id = Item.CategoryId) AS CategoryName
                      FROM Item 
                      LEFT OUTER JOIN Category ON Item.CategoryId = Category.Id'; 

    IF (@CategoriesIdString IS NOT NULL)
    BEGIN
        SET @SQLQuery += N' WHERE CHARINDEX('','' + CONVERT(VARCHAR(12), Item.CategoryId) + '','', '' +  @CategoriesIdString + '') > 0';
    END

    DECLARE @ParmDefinition NVARCHAR(600);
    SET @ParmDefinition = N'@categoriesId VARCHAR(1000);

    PRINT @SQLQuery;

    EXEC sp_executesql @SQLQuery, @ParmDefinition, @categoriesId = @CategoriesIdString

    RETURN 0

由于某种原因，这个过程没有返回任何记录.

This procedure, for some reason, returns no records.

但是，如果我运行以下查询:

However, if I run the following query:

DECLARE @CategoriesIdString VARCHAR(1000)
SET @CategoriesIdString = ',2,3,';

SELECT 
   Item.Id, Item.ModelId, Item.ItemCode, Item.CategoryId, 
    (SELECT TOP (1) Category.CategoryName 
     FROM Category 
     WHERE Category.Id = Item.CategoryId) AS CategoryName
FROM 
   Item 
LEFT OUTER JOIN             
   Category ON Item.CategoryId = Category.Id
WHERE 
   CHARINDEX(',' + CONVERT(VARCHAR(12), Item.CategoryId) + ',', @CategoriesIdString) > 0

然后我得到了一些行.

我在存储过程中做错了什么?

What do I do wrong in my stored procedure?

我也这样试过:

SET @SQLQuery += N' WHERE CHARINDEX('','' + CONVERT(VARCHAR(12), Item.CategoryId) + '','', @CategoriesIdString) > 0';

但得到一个错误:

{"必须声明标量变量\"@CategoriesIdString\"."}

{"Must declare the scalar variable \"@CategoriesIdString\"."}

推荐答案

在这种情况下，您不需要使用动态 T-SQL 语句.一种方法是使用这样的东西:

You do not need to use dynamic T-SQL statement in this case. One of the ways is to use something like this:

DECLARE @TempXML XML = CONVERT(XML, '<t>' + REPLACE(@CategoriesIdString, ',', '</t><t>') + '</t>')

;WITH DataSource ([CategoryId]) AS
(
    SELECT T.c.value('.', 'VARCHAR(100)')
    FROM @TempXML.nodes('/t') AS T(c)
    WHERE LEN(T.c.value('.', 'VARCHAR(100)')) > 0
)
SELECT Item.Id
      ,Item.ModelId
      ,Item.ItemCode
      ,Item.CategoryId
      ,(SELECT TOP (1) Category.CategoryName FROM Category WHERE Category.Id = Item.CategoryId) AS CategoryName
FROM Item 
LEFT OUTER JOIN Category 
    ON Item.CategoryId = Category.Id
LEFT JOIN DataSource
    ON Item.CategoryId = DataSource.CategoryId
WHERE DataSource.CategoryId IS NOT NULL 
    OR @CategoriesIdString IS NULL

这个想法是通过 @CategoriesIdString 参数过滤，如果它是 NOT NULL 使用 LEFT JOIN.

The idea is to filter by @CategoriesIdString parameter if it is NOT NULL using LEFT JOIN.

这篇关于无法执行 SQL Server 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！