• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1137
  • Last Modified:

Stored procedure passing multiple input values to a variable in select statement

Hello,
I have a stored procedure that prompts for a user input value as a variable which is then passed to a select statement. It works fine but only for a single value entered.  I would like to be able to offer the user to input multiple values into a single variable if possible. Example: A user would enter CHID as 1,2,3,5 as @chid and the select statement would be select * from db1 where CHID IN (@CHID): for all records that values of 1,2,3,5. Thanks. c
Procedure:

ALTER procedure [dbo].[sp_Master_1]
@CHID varchar(2)  -user enters 1,2,3,5
as
SELECT a, b, c
INTO [dbo].[A]
WHERE CHID IN ('1','2','3','5')
0
cbads
Asked:
cbads
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please find my procedure dbo.ParmsToList() on EE, and you can use it like this:
ALTER procedure [dbo].[sp_Master_1]
@CHID varchar(2000)  ---user enters 1,2,3,5
as
SELECT a, b, c
INTO [dbo].[A]
WHERE CHID IN (select value from dbo.ParmsTolist(@CHID, ',') )

Open in new window

0
 
cbadsAuthor Commented:
Thanks. I created the function for dbo.ParmsToList() and added the (select value from dbo.ParmsTolist(@CHID, ',') )
to my stored procedure but I get this message when I save it:

Msg 8144, Level 16, State 3, Procedure sp_Master_1, Line 117
Procedure or function dbo.ParmsTolist has too many arguments specified.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you got a version without the second argument.


ALTER procedure [dbo].[sp_Master_1]
@CHID varchar(2000)  ---user enters 1,2,3,5
as
SELECT a, b, c
INTO [dbo].[A]
WHERE CHID IN (select value from dbo.ParmsTolist(@CHID) )

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cbadsAuthor Commented:
Here's the link to the dbo.ParmsToList() I'm using...is this the wrong version?

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21627393.html

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
there are 2 versions, the one you copied, and one (in the "open discussion") contains the additional parameter version.

for your needs, you don't have to use that version  :)
0
 
cbadsAuthor Commented:
Thank You. I used the one in the open discussion and it's working perfectly.
cb
0
 
Lorna70Commented:
Sorry being a SQL newbie, I'm really struggling to get my head round this.  Thought I'd go for the simplest:

UPDATE tLoads
SET RouteID = @routeID
WHERE LoadID  IN
  (SELECT     Value
   FROM   dbo.ParmsToList(@loadIDList, ',') AS ParmsToList_1))

However, I don't know where ParmsToList comes from and this @loadIDList - is this an array and if so, how do I declare it in SQL?  
If someone could give me advice about learning this stuff, that would be great too.
Many thanks
Lorna
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
dbo.ParmsToList can be found here:
http://www.experts-exchange.com/A_1536.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now