如何解析传递给 SQL Server 中存储过程的 VARCHAR? [英] How to parse a VARCHAR passed to a stored procedure in SQL Server?

问题描述

我有两个表 tbl_Products 和 tbl_Brands，它们都连接在 BrandId 上.

I have two tables tbl_Products and tbl_Brands, both are joined on BrandId.

我有一个存储过程，它应该返回属于作为参数传递给它的品牌 ID 的所有产品.

I have a stored procedure which should return all the products belong to the brand ids passed to it as parameter.

我的代码如下.

create proc Sp_ReturnPrdoucts
    @BrandIds varchar(500) = '6,7,8'
AS
BEGIN
    SELECT * 
    FROM tbl_Products as p 
    JOIN tbl_Brands b ON p.ProductBrandId = b.BrandId 
    WHERE b.BrandId IN (@BrandIds)
END

但是这是错误的，因为 BrandId 是 INT 而 @BrandIds 是 VARCHAR

But this is giving error as BrandId is INT and @BrandIds is VARCHAR

当我以如下方式对其进行硬编码时，它可以正常工作并从 db 返回所需的数据 ..

When I hard code it this way as follows it works fine and returns the desired data from db ..

create proc Sp_ReturnPrdoucts
    @BrandIds varchar(500) = '6,7,8'
AS
BEGIN
    SELECT * 
    FROM tbl_Products AS p 
    JOIN tbl_Brands b ON p.ProductBrandId = b.BrandId 
    WHERE b.BrandId IN (6,7,8)
END

任何帮助:)

推荐答案

另一种选择是使用间接"(我一直这样称呼它)

Another alternative is to use 'indirection' (as I've always called it)

然后你可以做..

create proc Sp_ReturnPrdoucts
@BrandIds varchar(500) = '6,7,8'
AS
BEGIN
    if (isnumeric(replace(@BrandIds,',',''))=1) 
    begin
        exec('SELECT * FROM tbl_Products as p join tbl_Brands b on p.ProductBrandId=b.BrandId WHERE b.BrandId IN ('+@BrandIds+')')
    end
END

通过这种方式，select 语句被构建为一个字符串，然后被执行.

This way the select statement is built as a string, then executed.

我现在添加了验证以确保传入的字符串是纯数字(删除所有逗号后)

I've now added validation to ensure that the string being passed in is purely numeric (after removing all the commas)

这篇关于如何解析传递给 SQL Server 中存储过程的 VARCHAR?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！