RSS

create a SQL stored procedure with Mulitple parameters

20 Jul

Overview
This  stored procedures is the ability to pass Multiple parameters and have the stored procedure handle the differing requests that are made.

Explanation
This Sp specially develop for web site Searching Criteria .in this sp use multiple parameter  that get data according to your input and also use date parameter.

if date parameter empt then pass null to sp

stored procedure
ALTER Procedure [dbo].[mp_tkg_SearchingInquirywithparameters]
(
@name varchar(50)= null,
@email varchar(50)= null,
@contactreson varchar(50)= null,
@status varchar(2)= null,
@startdate datetime=null,
@enddate datetime=null

)
AS
BEGIN

If @name=null and Len(@name )=0 Set @name = ”
If @email=null and Len(@email )=0 Set @email = ”
If @status=null   and Len(@status )=0 Set @status = ”
If @contactreson=null and Len(@contactreson )=0 Set @contactreson = ”
if(@startdate !=null and @enddate !=null)
begin
select * from dbo.tkg_Inquiry where

( (tkg_Inquiry.FirstName LIKE ‘%’ + ISNULL(@name , tkg_Inquiry.FirstName) + ‘%’) or

(tkg_Inquiry.LastName LIKE ‘%’ + ISNULL(@name , tkg_Inquiry.FirstName) + ‘%’)) and

(tkg_Inquiry.Email LIKE ‘%’ + ISNULL(@email , tkg_Inquiry.Email) + ‘%’)and
(tkg_Inquiry.ContactReason LIKE ‘%’ + ISNULL(@contactreson , tkg_Inquiry.ContactReason) + ‘%’) and
(tkg_Inquiry.Processed LIKE ‘%’ + ISNULL(@status , tkg_Inquiry.Processed) + ‘%’) and
(Convert(varchar(10), dbo.tkg_Inquiry.DateCreated, 101) >= Convert(varchar(10), @startdate, 101)
and Convert(varchar(10), dbo.tkg_Inquiry.DateCreated, 101)<=Convert(varchar(10), @enddate, 101))
end
else
begin
select * from dbo.tkg_Inquiry where

( (tkg_Inquiry.FirstName LIKE ‘%’ + ISNULL(@name , tkg_Inquiry.FirstName) + ‘%’) or

(tkg_Inquiry.LastName LIKE ‘%’ + ISNULL(@name , tkg_Inquiry.FirstName) + ‘%’)) and

(tkg_Inquiry.Email LIKE ‘%’ + ISNULL(@email , tkg_Inquiry.Email) + ‘%’)and
(tkg_Inquiry.ContactReason LIKE ‘%’ + ISNULL(@contactreson , tkg_Inquiry.ContactReason) + ‘%’) and
(tkg_Inquiry.Processed LIKE ‘%’ + ISNULL(@status , tkg_Inquiry.Processed) + ‘%’)

end
END

Pass Parameter To SP

public static DataSet SearchingInquiry(string name, string email, string contactReason, string status, string startdate, string enddate)
{
if (startdate != “”)
{
enddate = startdate;
}

DataSet ds=new DataSet();
SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), “mp_tkg_SearchingInquirywithparameters”, 6);

sph.DefineSqlParameter(“@name”, SqlDbType.VarChar, 100, ParameterDirection.Input, name);
sph.DefineSqlParameter(“@Email”, SqlDbType.VarChar, 150, ParameterDirection.Input, email);
sph.DefineSqlParameter(“@contactreson”, SqlDbType.VarChar, 50, ParameterDirection.Input, contactReason);
sph.DefineSqlParameter(“@status”, SqlDbType.VarChar, 20, ParameterDirection.Input, status);
if (startdate == “”)
{

sph.DefineSqlParameter(“@startdate”, SqlDbType.DateTime, ParameterDirection.Input,null);

}
else
{

sph.DefineSqlParameter(“@startdate”, SqlDbType.DateTime, ParameterDirection.Input,  DateTime.Parse(startdate));

}
if (enddate == “”)
{
sph.DefineSqlParameter(“@enddate”, SqlDbType.DateTime, ParameterDirection.Input,null);

}
else
{

sph.DefineSqlParameter(“@enddate”, SqlDbType.DateTime, ParameterDirection.Input, DateTime.Parse(enddate));

}

ds = sph.ExecuteDataset();
return ds;

}

 

Advertisements
 
Leave a comment

Posted by on July 20, 2012 in SQL Query

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: