Solved

Send Parameter to SQL Server as delimeted argument for IN

Posted on 2007-03-27
3
177 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 142

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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now