Solved

Send Parameter to SQL Server as delimeted argument for IN

Posted on 2007-03-27
3
180 Views
Last Modified: 2010-04-23
Is it possible to send a string to sql server as a parameter for an IN sql statement.

Basically I am building the string as

'product1','product2','product3','product4'

The stored proc will be something like:

CREATE PROCEDURE dbo.GetProducts
(
@Products AS nvarchar(200)
)
AS
SELECT *
FROM Products
WHERE ProductID IN(@Products)
GO

Is this valid? if not what should the string format be if it is possible?

Wing
0
Comment
Question by:WingYip
3 Comments
 
LVL 43

Assisted Solution

by:TimCottee
TimCottee earned 25 total points
ID: 18798556
You cannot do this without using dynamic sql or a more complicated method involving xml as a string.

The easiest way is this:

CREATE PROCEDURE dbo.GetProducts
(
@Products AS nvarchar(200)
)
AS
Declare @Sql nvarchar(4000)
Set @Sql = 'SELECT * FROM Products WHERE ProductID IN(' + @Products +')'
Exec (@sql)
GO
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 18798557
create the following function:


CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
returns @result TABLE (Value varchar(30))
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(30)
          )

     DECLARE @Value varchar(30), @Pos int

     SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
     SET @Pos = CHARINDEX(',', @Parameters, 1)

     IF REPLACE(@Parameters, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1)

          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END    


then, change your procedure:

CREATE PROCEDURE dbo.GetProducts
(
@Products AS nvarchar(200)
)
AS
SELECT *
FROM Products
WHERE ProductID IN( select value from dbo.ParmstoList(@Products))
GO

and finally, pass the value in your code without quotes in the middle, like this
exec dbo.GetProducts 'product1,product2,product3,product4'

0
 
LVL 1

Author Comment

by:WingYip
ID: 18804944
Thanks alot.  Ended up using angelII suggestion but both answers appreciated.  Much obliged to both of you.

Thanks

Wing
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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