Handle multiple parameters in SQL Server

Question

I'm using a stored procedure to search in my database and filtering the results on a parameter and I need to filter on unknown multiple parameters.

How can I write a stored procedure that handles this??

Here is my stored procedure :

CREATE PROCEDURE ComplaintRefListOnDistrict
     @District nvarchar(max) = ''
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  
       ComplaintFullID, CustomerName, Customer_Address, CustomerEmail,
       Date, ContractID, CustomerPhoneNumber,ID, Complaintreference_ID, State
    FROM 
       dbo.ComplaintsSmartObject
    LEFT JOIN 
       dbo.UsersDistricts ON dbo.UsersDistricts.District = dbo.ComplaintsSmartObject.District
    WHERE
       (dbo.UsersDistricts.District = @District)
END
GO

Thank you!

Do you mean , what if @District contains "dist1,dist2", and how to get result based on that? — Arindam Nayak
– Arindam Nayak, Commented Sep 25, 2014 at 10:05
By parameter in Stored procedure we mean Input/ Output parameter. like in your case @District is input parameter. When you say multiple parameter i believe you mean multiple and unknown values in @District . If that's correct then please share the string which you wish to pass to this sproc. — Deepshikha
– Deepshikha, Commented Sep 25, 2014 at 10:30
as @ArindamNayak said, what if my Input Parameter '@District' has more than a value? — Mostafa Ab
– Mostafa Ab, Commented Sep 25, 2014 at 10:33
@MostafaAb , i am going to post solution for this specifc thing - more than one value, you need to use dynamic SQL to achive this. — Arindam Nayak
– Arindam Nayak, Commented Sep 25, 2014 at 10:42
@MostafaAb , Sorry for late answer post, however you can check my answer, it is pretty simple, no need to create a second function to get split value. — Arindam Nayak
– Arindam Nayak, Commented Sep 26, 2014 at 3:22

Pradeep Kumar · Accepted Answer · 2014-09-25 10:41:49Z

1

First add a helper function, that will split the string you pass to it into rows.

CREATE FUNCTION SplitString 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS 
    @output TABLE(Data NVARCHAR(MAX)) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 
    BEGIN 
        IF (@end = 0) SET @end = LEN(@string) + 1
        INSERT INTO @output (Data) VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
    END 
    RETURN 
END

And now everything is very easy. Use the SQL IN operator:

CREATE PROCEDURE ComplaintRefListOnDistrict
     @District nvarchar(max) = ''
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  
       ComplaintFullID, CustomerName, Customer_Address, CustomerEmail,
       Date, ContractID, CustomerPhoneNumber,ID, Complaintreference_ID, State
    FROM 
       dbo.ComplaintsSmartObject
    LEFT JOIN 
       dbo.UsersDistricts ON dbo.UsersDistricts.District = dbo.ComplaintsSmartObject.District
    WHERE
       (dbo.UsersDistricts.District IN (SELECT Data FROM dbo.SplitString(@District, ',')))
END

HTH.

edited Sep 25, 2014 at 10:41

answered Sep 25, 2014 at 10:35

Pradeep Kumar

7,0294 gold badges24 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mostafa Ab Over a year ago

You are awesome man, I wish i have enough reputation points to vote up, thank you! I appreciate it so much :)

Pradeep Kumar Over a year ago

@MostafaAb, Nice to know that it helped. :) You may mark this as the answer if it has fully answered your problem, so that it is helpful to others who come searching for the same problem.

Arindam Nayak · Accepted Answer · 2014-09-26 03:03:48Z

0

I was telling to use this kind of dynamic SQL.

  CREATE PROCEDURE ComplaintRefListOnDistrict
         @District nvarchar(max) = ''
    AS
    BEGIN
        SET NOCOUNT ON;

     declare @sql nvarchar(max)

    set @sql = 'SELECT  
           ComplaintFullID, CustomerName, Customer_Address, CustomerEmail,
           Date, ContractID, CustomerPhoneNumber,ID, Complaintreference_ID, State
        FROM 
           dbo.ComplaintsSmartObject
        LEFT JOIN 
           dbo.UsersDistricts ON dbo.UsersDistricts.District = dbo.ComplaintsSmartObject.District
        WHERE
           (dbo.UsersDistricts.District in (' +  @District + ') )'

    EXEC sp_executesql @sql
    END

answered Sep 26, 2014 at 3:03

Arindam Nayak

7,4924 gold badges36 silver badges51 bronze badges

2 Comments

Arindam Nayak Over a year ago

Here you don't have to create a util function to split and get values in tabular manner.

Arindam Nayak Over a year ago

For this to work , you need to pass @District as quoted values with comma separated, if districts are "dist1", "dist2", you need to invoke this sp as EXEC ComplaintRefListOnDistrict '''dist1'',''dist2''' And if that is integer value, you don't have to put single quote.

mohan111 · Accepted Answer · 2014-09-25 10:11:03Z

0

CREATE PROCEDURE ComplaintRefListOnDistrict
(
 @District nvarchar(max) = NULL
)
AS
BEGIN

SET NOCOUNT ON;
SELECT 
CSC.ComplaintFullID, CSC.CustomerName, CSC.Customer_Address, CSC.CustomerEmail,
CSC.Date, UD.ContractID, UD.CustomerPhoneNumber,UD.ID, UD.Complaintreference_ID, UD.State
FROM 
  dbo.ComplaintsSmartObject CSC
  LEFT JOIN dbo.UsersDistricts UD
    ON UD.District = CSC.District
WHERE
 (
            UD.District LIKE '%' + @District + '%'
            OR @District IS NULL
            OR @District = ''
            )

    ORDER BY CSC.ComplaintFullID, 
    CSC.CustomerName, 
    CSC.Customer_Address, 
    CSC.CustomerEmail,
CSC.Date, UD.ContractID, UD.CustomerPhoneNumber,UD.ID, UD.Complaintreference_ID, UD.State


END
GO

answered Sep 25, 2014 at 10:11

mohan111

8,8854 gold badges35 silver badges61 bronze badges

2 Comments

Mostafa Ab Over a year ago

maybe I didn't explain it well, my stored procedure receives one parameter, I need a stored procedure that Receive unknown number of parameters, to be clear I have a dropdown list has unstable items, how can I let my stored procedure take them without specifying how many parameter in it? I wanna pass them in one go no matter how many they are, is it possible? notice that this procedure receives only one item. hope its clear

mohan111 Over a year ago

you are declaring only one parameter and in select statement you are asking to search the required data.So i gave search in where condition

Oscar · Accepted Answer · 2014-09-25 10:13:11Z

0

You can create a WHERE clause where you use the unknow parameter or check if it's null

WHERE (dbo.UsersDistricts.District = @District OR District IS NULL)
AND (SomeOtherColumn = @OtherParameter OR OtherParameter IS NULL)

And so on.

answered Sep 25, 2014 at 10:13

Oscar

14.1k9 gold badges53 silver badges84 bronze badges

Collectives™ on Stack Overflow

Handle multiple parameters in SQL Server

4 Answers 4

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related