CODEWITHSUNDEEP
sqlsql-serversql-server-2008
mzh
mzh

Reputation: 525

How to parse a VARCHAR passed to a stored procedure in SQL Server?

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

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

My code is as follows. 

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

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

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

Any help :)

Upvotes: 1

Views: 769

Answers (2)

Rich S
Rich S

Reputation: 3453

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

You can then do..

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

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)

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82474

If possible, don't use varchar for this kind of things, use a table valued parameter instead.

To use a tabled value parameter you should first declare a user defined table type:

CREATE TYPE IntList As Table
(
    IntValue int
)

Then change your stored procedure to accept this variable instead of the nvarchar:

create proc Sp_ReturnPrdoucts
    @BrandIds dbo.IntList readonly -- Note: readonly is a must!
AS
BEGIN

SELECT * 
FROM tbl_Products as p 
join tbl_Brands b on p.ProductBrandId=b.BrandId 
join @BrandIds ON(b.BrandId = IntValue)

END

The problem is that the IN() operator expects a list of variables separated by commas, while you provide a single variable that it's value is a comma separated string.

If you can't use a table valued parameter, you can use a string spliting function in sql to convert the value of the varchar to a table of ints. there are many splitters out there, I would recommend reading this article before picking one.

Upvotes: 4

Related Questions