CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlstored-procedures
Aditya Shrivastava
Aditya Shrivastava

Reputation: 329

how to pass multiple values in where clause for single argument

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.

Upvotes: 1

Views: 8270

Answers (2)

Amit
Amit

Reputation: 46323

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.

Upvotes: 2

na-98
na-98

Reputation: 889

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!

Upvotes: 2

Related Questions