how to pass multiple values in where clause for single argument

Question

I have faced this situation first time. Here I have to pass multiple values for a single argument and also it is not necessary that the argument will always have multiple values. Sometimes 1 sometimes it will be 2 or also 3. So how do I write it in where clause I am confused in that. I have looked for the various source but I am not geting how to I do exactly this thing. Someone suggests to use dynamic query but is there any easy way to do it. I will post a pseudo code please suggests me how should do it or what the best way to this.

Sample code

IF OBJECT_ID('db.pro_total', 'P') IS NOT NULL
DROP PROCEDURE db.pro_total;
go

create procedure db.pro_total (@value1  int,
                                     @ value2 varchar(50),
                                     @ value3 int,
                                     @ value4 varchar (10),
                                     @ value5 varchar(100)
                                            )
as
begin

select sum(column6) as recived
where status_id=1
and column1 =@ value1
and column2=@value2
and column3=@ value3  
and column4 =@ value4
and column5=@ value5
and time_id between 20150824 and 20150831
group by column1, column2,column3,column4, column5

 end;
  go

Like for @value2 I want to pass multiple values so how to I pass this and will it change how to call the procedure or the syntax would remain the same

Calling the procedure

execute db.pro_tota  '1','119','5400','PA','05L0038663710'

Any help is welcomed and if you don't understand any part just comment.

You can use the comma sepearated values for @value2 and then use the IN clause instead of = — Rahul Tripathi, Oct 16 '15 at 09:02
@RahulTripathi And how would `IN` work? It would still see a single value. — DavidG, Oct 16 '15 at 09:03
1) Use function which will separate `,` values 2) you can use XML path — wiretext, Oct 16 '15 at 09:05
Instead of passing a string of values, use a table-typed parameter, fill it with the values you need and change your query to join with the TVP just like any other table — Panagiotis Kanavos, Oct 16 '15 at 10:05
When you ask a question, you do that because you want other people to spend their time helping you. Don't change your question - doing so is disrespectful. It's OK to fix formatting, removed clutter, add relevant information that wasn't clear according to comments - but not to change the question, particularly not after you've been answered. I've rolled back your edit. Ask a new question if you want a different answer. — Amit, Oct 16 '15 at 13:09

score 2 · Accepted Answer · edited May 23 '17 at 12:07

Copying this from another Stackoverflow answer (https://stackoverflow.com/a/10914602/3007014) but tweaking it for your convenience.

Create a function to split comma separated values like this (you only have to create this once) -

    CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

You can then use it as

SELECT * FROM dbo.splitstring('1,2,3')

So for your case -

IF OBJECT_ID('db.pro_total', 'P') IS NOT NULL
DROP PROCEDURE db.pro_total;
go

create procedure db.pro_total (@value1  int,
                                     @ value2 varchar(50),
                                     @ value3 int,
                                     @ value4 varchar (10),
                                     @ value5 varchar(100)
                                            )
as
begin

select sum(column6) as recived
where status_id=1
and column1 =@ value1
and column2 in (select * from dbo.splitstring(@value2))
and column3=@ value3  
and column4 =@ value4
and column5=@ value5
and time_id between 20150824 and 20150831
group by column1, column2,column3,column4, column5

 end;
  go

Now you can call your proc like this -

execute db.pro_tota  '1','119,223,234','5400','PA','05L0038663710'

Note that your column 2 is defined as varchar(50). You haven't shown your table DDL so you want to make sure you match those and also only call the proc with <=50 characters including commas.

I haven't tested this though so try it out. I hope you get the idea. Good luck!

thank you but I have another issue can you please look – Aditya Shrivastava Oct 16 '15 at 11:28 — Aditya Shrivastava, Oct 16 '15 at 11:28

score 2 · Answer 2 · answered Oct 16 '15 at 09:35

The correct way to do that is using a Table Valued Parameter.

What this means is that you use a user-defined table types as the input parameter, where you use multiple rows for multiple values. Then you use a standard INNER JOIN clause to filter your results.

To define the table type:

CREATE TYPE Val1Values AS TABLE 
    (val1 INT)

To declare a variable of this type, and fill with values:

DECLARE @val1Values AS Val1Values

INSERT INTO @val1Values
  VALUES(1), (3), (4)

And the stored procedure is:

CREATE PROCEDURE SelectWithValues
    @TVal1 Val1Values READONLY,
    @val2 int
    AS 
    SELECT Table1.*
    FROM Table1 INNER JOIN @TVal1 tv ON
      Table1.val1 = tv.val1
    WHERE Table1.val2 = @val2

So we're taking a variable of the table type as an input, using INNER JOIN as a filter, and using WHERE for "simple" filters as usual.

Also see this live on SQLFiddle.

great! but I had already done that can you help me the edited part — Aditya Shrivastava, Oct 16 '15 at 11:31

how to pass multiple values in where clause for single argument

2 Answers2