Multiple values parameter for a stored procedure

Posted by Nerdanalysis under Sql Server on 8/19/2014 | Points: 10 | Views : 6231 | Status : [Member] | Replies : 8
Hi,

how to pass multiple value parameter for a stored procedure? I have a screen, where the user can select multiple search selections they are about 15 of them. Based upon dynamic selected options,I need to show the results. Now the problem is :-

1. User may select any no of parameters eg only 2 out of 15 parameters. so the stored procedure should work with dynamic selection of parameters.

2. Instead of passing many parameters I want to pass only one parameters that will contain all the selected parameters.

There are various solutions on the web to pass a list of parameters as a single parameter. However that target to only one column eg. empIDs = "1,2,3,4,5" this will give details of all the employees whoes ids are in parameter passed. However in my case it should pass empid, date_of_join, Date_of_birth etc.



Any help will be highly appreciated.

Kind regards
Rakesh




Responses

Posted by: Bandi on: 8/20/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
for this kind of requirement, usually we will create SP by passing 15 parameters as input and then in the SP logic, we can handle all conditions or either of the conditions in the WHERE Clause...

If you are okay with passing 15 params, i can help you.. Otherwise clear me why u would like to pass only one param with multiple search criteria?


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/20/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
create table PassMultipleParams( ID INT identity(1,1), name varchar(100), salary dec(8,2), dept int, commisn dec(5,2))
/*PassMultipleParams Data
Last_Name Salary Department_Id Commission_PCT
King 24000.00 90 NULL
Kochhar 17000.00 90 NULL
De Haan 17000.00 90 NULL
Hunold 9000.00 60 NULL
Ernst 6000.00 60 NULL
Austin 4800.00 60 NULL
Pataballa 4800.00 60 NULL
Lorentz 4200.00 60 NULL
Greenberg 12000.00 100 NULL
Faviet 9000.00 100 NULL */

GO
CREATE PROCEDURE usp_SearchTable
( @pID INT,
@pName varchar(100),
@pSalary INT,
@pDept INT,
@pComm DEC(5,2))
AS
SELECT *
FROM PassMultipleParams
WHERE (ID = @pID OR @pID IS NULL)
AND ( name like '%'+@pName+'%' OR @pName IS NULL)
AND ( salary = @pSalary OR @pSalary IS NULL)
AND ( dept = @pDept OR @pDept IS NULL)
AND ( commisn = @pComm OR @pComm IS NULL)
GO
--Test SP usp_SearchTable
EXEC usp_SearchTable 2, NULL, NULL, NULL, NULL
EXEC usp_SearchTable NULL, NULL, 9000, NULL, NULL
EXEC usp_SearchTable 4, NULL, 9000, NULL, NULL
EXEC usp_SearchTable 4, 'uno', 9000, NULL, NULL
GO
--clean up
DROP PROCEDURE usp_SearchTable
DROP TABLE PassMultipleParams


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nerdanalysis on: 8/21/2014 [Member] Starter | Points: 25

Up
0
Down
Thanks Chandu for your time, also how to pass all the above parameters as one mufti-valued parameters.

C# code :- var Parameter = '1, Mr.Chandu, 9999, 1001, 15555.25'; and pass this string "Parameter" to stored procedure as a parameter

exec usp_SearchTable @MultiValueParameter


CREATE PROCEDURE usp_SearchTable ( @MultiValueParameter varchar(max))


now what i was thinking we can call a function to get different values from the comma delimited string and store into a table variable. eg

DECLARE @MultiValueParameter TABLE
(
id int IDENTITY(1,1),
SingleParameters VARCHAR(256)
);
INSERT INTO @MultiValueParameter
SELECT Item FROM fnConvert_String_to_Table_Strings(',', @MultiValueParameter)

Now can we get the values from the table and use in our search condition's where clause.

Thanks once again!
Rakesh


Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/21/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Simply you can pass multiple param values from front-end and then call SP in C# code..

For your reference, http://www.java2s.com/Tutorial/CSharp/0560__ADO.Net/CallstoredprocedurewithparametersusingSqlCommand.htm

while passing search criteria, pass DBNull param if any of the search condition is NULL

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/21/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
private DataTable Search()
{

//DataTable to hold search resutls
DataTable SearchResultsTable = new DataTable();
//Read connectionString from web.config
SqlConnection conn = new SqlConnection(ConnectionString);

try
{
//Create SQL command object by passing
//connection and stored procedure name
SqlCommand cmd = new SqlCommand("spSearch", conn);
//command type to Stored procedure
cmd.CommandType = CommandType.StoredProcedure;

//pass search criteria using parameters
cmd.Parameters.AddWithValue("@firstName",txtFirstName.Text);
cmd.Parameters.AddWithValue("@lastName" , txtLastName.Text);
cmd.Parameters.AddWithValue("@email " , txtEmail.Text);

//create sql adapter by passing command object
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//fill the search results table
adapter.Fill(SearchResultsTable);
}

catch (Exception ex)
{
Response.Write(ex.ToString();
}
finally
{
if (conn != null)
{
conn.Close();
}
}

//Return search results DataTable
return SearchResultsTable ;
}

}


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nerdanalysis on: 8/21/2014 [Member] Starter | Points: 25

Up
0
Down
Hi Chandu Thanks again,

all i am looking for to pass only one parameter rather than many


create table PassMultipleParams( ID INT identity(1,1), name varchar(100), salary dec(8,2), dept int, com dec(5,2))


Declare @MultiValueParameter
set @MultiValueParameter = '1, Mr.Chandu, 9999, 1001, 15555.25'

exec usp_SearchTable @MultiValueParameter

The above stored procedure will generate exact result as below select statement
select * from PassMultipleParams where id = 1 and name ='Mr.Chandu' and salary = 9999 and dept= 1001, com = 15555.25


//again I dont want to pass different parameters as "EXEC usp_SearchTable 2, NULL, NULL, NULL, NULL" just one parameter which will have all values.

Thanks Rakesh






Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naveenhcl on: 9/3/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

For this you need to create input parameter as null-able type.

create procedure proc_name
(
@Param1 varchar(100)=null,
@Param2 varchar(100)=null
)
as
begin
if @Param1 is not null and @Param2 is not null
begin
select * from tablename
where col1=@Param1 and col2=@Param2
end
else if @Param1 is not null
begin
select * from tablename
where col1=@Param1
end
else if @Param2 is not null
begin
select * from tablename
where col2=@Param2
end
else
begin
select * from tablename
end
end


Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Manub22 on: 7/10/2015 [Member] Starter | Points: 25

Up
0
Down
Check my blog post where I've discussed on 4 methods, by using:
1. CSV list
2. XML string
3. temp-tables
4. TVPs

Check here http://sqlwithmanoj.com/2015/07/10/passing-multiple-values-with-a-single-parameter-in-a-stored-procedure-sql-server/

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response