How to pass string to stored procedure

Question

My stored procedure is:

ALTER Proc [hometution].[Sp_GetHomePageProducts]
    @CatIds nvarchar(500)
as
begin
    Select Top 3 *  
    from   Product p 
    where  p.Id in
        (Select ProductId 
         from   Product_Category_Mapping PCM 
         where  PCM.CategoryId in    (@CatIds))  
      and  p.ShowOnHomePage=1 
      and p.Deleted=0 
    order by UpdatedOnUtc
end

I am calling it like this

exec Sp_GetHomePageProducts @CatIds='17,12'

I am getting error

Conversion failed when converting the nvarchar value '17,12' to data type int.

Use one of the many split functions to turn your parameter into multiple rows — Andomar
– Andomar, Commented May 21, 2013 at 10:04

Devart · Accepted Answer · 2013-05-21 10:08:02Z

1

Try this one -

ALTER PROC [hometution].[Sp_GetHomePageProducts] 

@CatIds NVARCHAR(500)

AS
BEGIN

    ;WITH cte AS 
    (
        SELECT id = p.value('(./s)[1]', 'INT') 
        FROM (
            SELECT field = CAST('<r><s>' + REPLACE(@CatIds, ',', '</s></r><r><s>') + '</s></r>' AS XML) 
        ) d
        CROSS APPLY field.nodes('/r') t(p)
    )
    SELECT TOP 3 *
    FROM dbo.product p
    WHERE p.id IN (
            SELECT PCM.ProductId
            FROM dbo.Product_Category_Mapping PCM
            JOIN cte c ON c.id = PCM.CategoryId 
        )
        AND p.ShowOnHomePage = 1 
        AND p.Deleted = 0
    ORDER BY UpdatedOnUtc

END

answered May 21, 2013 at 10:08

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass string to stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related