?
Solved

Pass comma delimited IN to a stored procedure

Posted on 2006-05-14
8
Medium Priority
?
487 Views
Last Modified: 2008-03-10
Hi guys,
here is my Stored Procedure:

CREATE PROCEDURE [hostato_info4trade].[sp_searchProdsByCat] (@iType char(1), @catsArr varchar(100)) AS
SELECT     tblProds.id AS prodId, tblProds.title AS prodName, tblProds.picFile AS prodPicFile, tblProds.iPrice AS iPrice, tblSuppliers.id AS supplierId,
                      tblSuppliers.name AS supplierName, tblSuppliers.picFile AS supplierPicFile
FROM         tblProds INNER JOIN
                      tblUsers ON tblProds.userId = tblUsers.id INNER JOIN
                      tblProdsInCats ON tblProds.id = tblProdsInCats.prodId INNER JOIN
                      tblCats ON tblProdsInCats.catId = tblCats.id INNER JOIN
                      tblSuppliers ON tblUsers.id = tblSuppliers.userId LEFT OUTER JOIN
                      tblSelectedProdsCats ON tblProds.id = tblSelectedProdsCats.prodId AND tblCats.id = tblSelectedProdsCats.catId
WHERE     (tblProds.iType = @iType) AND (tblProdsInCats.catId in (@catsArr))
ORDER BY tblSelectedProdsCats.priority, tblProds.picFile, tblSuppliers.picFile, tblProds.iDate
GO

I want to pass @catsArr as a comma delimited for use in the IN() query.

WORKS:
exec sp_searchProdsByCat "S","1133"

Doesn't work:
exec sp_searchProdsByCat "S","1133,1133"
the error:
Server: Msg 245, Level 16, State 1, Procedure sp_searchProdsByCat, Line 2
Syntax error converting the varchar value '1133,1133' to a column of data type int.

Please advice,
Daniel Engelman
0
Comment
Question by:webiz
8 Comments
 
LVL 15

Expert Comment

by:mcmonap
ID: 16678401
Hi webiz,

Try something like this, you cannot do it as you want, but you could create a dynamic SQL statement and then exectute it, sorry if the syntax is incorrect I don't have a sql server to test on at the moment:

CREATE PROCEDURE [hostato_info4trade].[sp_searchProdsByCat] (@iType char(1), @catsArr varchar(100))
AS
DECLARE @sSQL VARCHAR(8000)

SET @sSQL = 'SELECT     tblProds.id AS prodId, tblProds.title AS prodName, tblProds.picFile AS prodPicFile, tblProds.iPrice AS iPrice, tblSuppliers.id AS supplierId,
                      tblSuppliers.name AS supplierName, tblSuppliers.picFile AS supplierPicFile
FROM         tblProds INNER JOIN
                      tblUsers ON tblProds.userId = tblUsers.id INNER JOIN
                      tblProdsInCats ON tblProds.id = tblProdsInCats.prodId INNER JOIN
                      tblCats ON tblProdsInCats.catId = tblCats.id INNER JOIN
                      tblSuppliers ON tblUsers.id = tblSuppliers.userId LEFT OUTER JOIN
                      tblSelectedProdsCats ON tblProds.id = tblSelectedProdsCats.prodId AND tblCats.id = tblSelectedProdsCats.catId
WHERE     (tblProds.iType = ''' + @iType + ''') AND (tblProdsInCats.catId in (' + @catsArr + '))
ORDER BY tblSelectedProdsCats.priority, tblProds.picFile, tblSuppliers.picFile, tblProds.iDate'

EXEC @sSQL

GO
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 16678699
The parameter comes through as a single Varchar, rather than the list you would code in a SQL statement.
An approach that would work is to split the parameter in your procedure to insert the separate values into a temporary table, and then use a select on the values from this table in your IN clause.

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 16679521
Another method  without using dynamic Query,Use the following function to extract the contents of the csv

CREATE  function fn_ParseCSVString(
@CSVString     varchar(8000) )
returns @tbl table (s varchar(1000))
as
/*
select * from dbo.fn_ParseCSVString ('1,4,7,3,5')
*/
begin
declare @i int ,
    @j int
    select     @i = 1
    while @i <= len(@CSVString)
    begin
        select    @j = charindex(',', @CSVString, @i)
        if @j = 0
        begin
            select    @j = len(@CSVString) + 1
        end
        insert    @tbl select substring(@CSVString, @i, @j - @i)
        select    @i = @j + 1
    end
    return
end





NOW modify your Query as follows

> WHERE     (tblProds.iType = @iType) AND (tblProdsInCats.catId in (@catsArr))
> ORDER BY tblSelectedProdsCats.priority, tblProds.picFile, tblSuppliers.picFile,
> tblProds.iDate


WHERE     (tblProds.iType = @iType) AND (tblProdsInCats.catId in (SELECT S FROM dbo.fn_ParseCSVString (@catsArr) ))
ORDER BY tblSelectedProdsCats.priority, tblProds.picFile, tblSuppliers.picFile, tblProds.iDate
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:rommeljohnsantos
ID: 16680800
You may also use the REPLACE function..

.
.
.
.
where . . . . .  AND (tblProdsInCats.catId in (' + REPLACE(@catsArr,  ''',''') + ') . . . . .


Hope this helps... :)
0
 
LVL 1

Author Comment

by:webiz
ID: 16683621
aneeshattingal, your solutions seems great (espacially because it's generic)
but when I am trying to add your script:

CREATE  FUNCTION [hostato_info4trade].[fnk_ParseCSVString] (@CSVString     varchar(8000))
returns @tbl table (s varchar(1000))
as
/*
select * from hostato_info4trade.fn_ParseCSVString ('1,4,7,3,5')
*/
begin
declare @i int ,
    @j int
    select     @i = 1
    while @i <= len(@CSVString)
    begin
        select    @j = charindex(',', @CSVString, @i)
        if @j = 0
        begin
            select    @j = len(@CSVString) + 1
        end
        insert    @tbl select substring(@CSVString, @i, @j - @i)
        select    @i = @j + 1
    end
    return
end

it says: Error 21037: [SQL-DMO]The name specified in the Text property's 'CREATE ...' statement must match the Name property, and must be followed by valid TSQL statements.

That means TOTAL GIBBERISH, lol :)

please advice,
Daniel Engelman
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16687751
I didnt get any errors. are you creating in EM, otherwise try to create using the QA
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16688749
Did u solve that error ?
0
 
LVL 1

Author Comment

by:webiz
ID: 16688782
Yeah, I have ran it from SQL Query Analyzer...
Thank you very much, it works great.

Regards,
Daniel Engelman, Israel.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question