Suggested Videos
Part 65 - List all tables in a sql server database using a query
Part 66 - Writing re-runnable sql server scripts
Part 67 - Alter database table columns without dropping table
Parameters of a sql server stored procedure can be made optional by specifying default values.
We wil be using table tblEmployee for this Demo.
CREATE TABLE tblEmployee
(
Id int IDENTITY PRIMARY KEY,
Name nvarchar(50),
Email nvarchar(50),
Age int,
Gender nvarchar(50),
HireDate date,
)
Insert into tblEmployee values
('Sara Nan','Sara.Nan@test.com',35,'Female','1999-04-04')
Insert into tblEmployee values
('James Histo','James.Histo@test.com',33,'Male','2008-07-13')
Insert into tblEmployee values
('Mary Jane','Mary.Jane@test.com',28,'Female','2005-11-11')
Insert into tblEmployee values
('Paul Sensit','Paul.Sensit@test.com',29,'Male','2007-10-23')
Name, Email, Age and Gender parameters of spSearchEmployees stored procedure are optional. Notice that, we have set defaults for all the parameters, and in the "WHERE" clause we are checking if the respective parameter IS NULL.
Create Proc spSearchEmployees
@Name nvarchar(50) = NULL,
@Email nvarchar(50) = NULL,
@Age int = NULL,
@Gender nvarchar(50) = NULL
as
Begin
Select * from tblEmployee where
(Name = @Name OR @Name IS NULL) AND
(Email = @Email OR @Email IS NULL) AND
(Age = @Age OR @Age IS NULL) AND
(Gender = @Gender OR @Gender IS NULL)
End
Testing the stored procedure
1. Execute spSearchEmployees -- This command will return all the rows
2. Execute spSearchEmployees @Gender = 'Male' -- Retruns only Male employees
3. Execute spSearchEmployees @Gender = 'Male', @Age = 29 -- Retruns Male employees whose age is 29
This stored procedure can be used by a search page that looks as shown below.
WebForm1.aspx:
<table style="font-family:Arial; border:1px solid black">
<tr>
<td colspan="4" style="border-bottom: 1px solid black">
<b>Search Employees</b>
</td>
</tr>
<tr>
<td>
<b>Name</b>
</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
<td>
<b>Email</b>
</td>
<td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<b>Age</b>
</td>
<td>
<asp:TextBox ID="txtAge" runat="server"></asp:TextBox>
</td>
<td>
<b>Gender</b>
</td>
<td>
<asp:DropDownList ID="ddlGender" runat="server">
<asp:ListItem Text="Any Gender" Value="-1"></asp:ListItem>
<asp:ListItem Text="Male" Value="Male"></asp:ListItem>
<asp:ListItem Text="Female" Value="Female"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td colspan="4">
<asp:Button ID="btnSerach" runat="server" Text="Search"
onclick="btnSerach_Click" />
</td>
</tr>
<tr>
<td colspan="4">
<asp:GridView ID="gvEmployees" runat="server">
</asp:GridView>
</td>
</tr>
</table>
WebForm1.aspx.cs:
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetData();
}
}
protected void btnSerach_Click(object sender, EventArgs e)
{
GetData();
}
private void GetData()
{
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spSearchEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
AttachParameter(cmd, "@Name", txtName);
AttachParameter(cmd, "@Email", txtEmail);
AttachParameter(cmd, "@Age", txtAge);
AttachParameter(cmd, "@Gender", ddlGender);
con.Open();
gvEmployees.DataSource = cmd.ExecuteReader();
gvEmployees.DataBind();
}
}
private void AttachParameter(SqlCommand command, string parameterName, Control control)
{
if (control is TextBox && ((TextBox)control).Text != string.Empty)
{
SqlParameter parameter = new SqlParameter(parameterName, ((TextBox)control).Text);
command.Parameters.Add(parameter);
}
else if (control is DropDownList && ((DropDownList)control).SelectedValue != "-1")
{
SqlParameter parameter = new SqlParameter parameterName, ((DropDownList)control).SelectedValue);
command.Parameters.Add(parameter);
}
}
}
Make sure you have the following using statements in your code-behind page
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
Part 65 - List all tables in a sql server database using a query
Part 66 - Writing re-runnable sql server scripts
Part 67 - Alter database table columns without dropping table
Parameters of a sql server stored procedure can be made optional by specifying default values.
We wil be using table tblEmployee for this Demo.
CREATE TABLE tblEmployee
(
Id int IDENTITY PRIMARY KEY,
Name nvarchar(50),
Email nvarchar(50),
Age int,
Gender nvarchar(50),
HireDate date,
)
Insert into tblEmployee values
('Sara Nan','Sara.Nan@test.com',35,'Female','1999-04-04')
Insert into tblEmployee values
('James Histo','James.Histo@test.com',33,'Male','2008-07-13')
Insert into tblEmployee values
('Mary Jane','Mary.Jane@test.com',28,'Female','2005-11-11')
Insert into tblEmployee values
('Paul Sensit','Paul.Sensit@test.com',29,'Male','2007-10-23')
Name, Email, Age and Gender parameters of spSearchEmployees stored procedure are optional. Notice that, we have set defaults for all the parameters, and in the "WHERE" clause we are checking if the respective parameter IS NULL.
Create Proc spSearchEmployees
@Name nvarchar(50) = NULL,
@Email nvarchar(50) = NULL,
@Age int = NULL,
@Gender nvarchar(50) = NULL
as
Begin
Select * from tblEmployee where
(Name = @Name OR @Name IS NULL) AND
(Email = @Email OR @Email IS NULL) AND
(Age = @Age OR @Age IS NULL) AND
(Gender = @Gender OR @Gender IS NULL)
End
Testing the stored procedure
1. Execute spSearchEmployees -- This command will return all the rows
2. Execute spSearchEmployees @Gender = 'Male' -- Retruns only Male employees
3. Execute spSearchEmployees @Gender = 'Male', @Age = 29 -- Retruns Male employees whose age is 29
This stored procedure can be used by a search page that looks as shown below.
WebForm1.aspx:
<table style="font-family:Arial; border:1px solid black">
<tr>
<td colspan="4" style="border-bottom: 1px solid black">
<b>Search Employees</b>
</td>
</tr>
<tr>
<td>
<b>Name</b>
</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
<td>
<b>Email</b>
</td>
<td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<b>Age</b>
</td>
<td>
<asp:TextBox ID="txtAge" runat="server"></asp:TextBox>
</td>
<td>
<b>Gender</b>
</td>
<td>
<asp:DropDownList ID="ddlGender" runat="server">
<asp:ListItem Text="Any Gender" Value="-1"></asp:ListItem>
<asp:ListItem Text="Male" Value="Male"></asp:ListItem>
<asp:ListItem Text="Female" Value="Female"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td colspan="4">
<asp:Button ID="btnSerach" runat="server" Text="Search"
onclick="btnSerach_Click" />
</td>
</tr>
<tr>
<td colspan="4">
<asp:GridView ID="gvEmployees" runat="server">
</asp:GridView>
</td>
</tr>
</table>
WebForm1.aspx.cs:
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetData();
}
}
protected void btnSerach_Click(object sender, EventArgs e)
{
GetData();
}
private void GetData()
{
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spSearchEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
AttachParameter(cmd, "@Name", txtName);
AttachParameter(cmd, "@Email", txtEmail);
AttachParameter(cmd, "@Age", txtAge);
AttachParameter(cmd, "@Gender", ddlGender);
con.Open();
gvEmployees.DataSource = cmd.ExecuteReader();
gvEmployees.DataBind();
}
}
private void AttachParameter(SqlCommand command, string parameterName, Control control)
{
if (control is TextBox && ((TextBox)control).Text != string.Empty)
{
SqlParameter parameter = new SqlParameter(parameterName, ((TextBox)control).Text);
command.Parameters.Add(parameter);
}
else if (control is DropDownList && ((DropDownList)control).SelectedValue != "-1")
{
SqlParameter parameter = new SqlParameter parameterName, ((DropDownList)control).SelectedValue);
command.Parameters.Add(parameter);
}
}
}
Make sure you have the following using statements in your code-behind page
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
Create Proc spSearchEmployees
ReplyDelete@Name nvarchar(50) = NULL,
@Email nvarchar(50) = NULL,
@Age int = NULL,
@Gender nvarchar(50) = NULL
as
Begin
Select * from tblEmployee where
Name=ISNULL(@Name,Name)
AND Email=ISNULL(@Email ,Email)
AND Age =ISNULL(@Age ,Age )
AND Gender =ISNULL(@Gender,Gender)
End
or You can replace isnull by coalesce its work same
but personally i use isnull for better performance
Create Proc spSearchEmployees
Delete@Name nvarchar(50),
@Email nvarchar(50),
@Age int,
@Gender nvarchar(50)
as
Begin
Select * from tblEmployee where
Name=ISNULL(@Name,Name)
AND Email=ISNULL(@Email ,Email)
AND Age =ISNULL(@Age ,Age )
AND Gender =ISNULL(@Gender,Gender)
End
--CALLING SP
exec spSearchEmployees NULL, NULL, NULL, NULL
without optional parameters also it works
what is the difference?
Thank you sir for such a good article. I have the same kind of work and came across one problem. It is a cascaded drop down list like country, state and city. The app works fine for name, age and country search with and without values but not for state and city. I could not figure out the problem. Any solution?
ReplyDeleteHi, how to use optional where clause if it has Date range "Between" clause??
ReplyDeleteI'm having a doubt that if we are passing null in where clause then how it's returning all the results?
ReplyDeleteWhen I try to run the ASP.NET program, I get:
ReplyDeleteHTTP Error 403.14 - Forbidden
The Web server is configured to not list the contents of this directory.
Most likely causes:
A default document is not configured for the requested URL, and directory browsing is not enabled on the server.
How do you enable directory browsing?