如何解析传递给 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屋!