Recently I had been on an interview and this was the question that I was asked about SQL Server which I would like to share with all of you. Since I found this question interesting, I hope you will also. The question was something like this.
In our front end we are having a search criteria form where a user is able to search a particular user as per his or her name or the user can search by city or by entering both username and city of that user. If the user does not any value for both the name and city then all the details should be displayed or else respective details fulfilling that criteria should be displayed.
Here is the snapshot of it:
![Search-record1.jpg]()
Here when the user enters the name or the city or both, depending on the data, the respective user should be displayed. And if the user does not enter anything then all data needs to be displayed. The main logic which is used for developing the procedure is that we don't need to use if/else in the procedure, because if we use if/else then if after some time if we need to add some more fields then the number of if/else conditions increase. So we have to program it inside the where clause itself. Even though after writing it in the where clause the number of conditions increase corresponding to the fields, but since it was asked, let me tell you how you can do it.
![Search-record2.jpg]()
<!--[endif]-->
![Search-record3.jpg]()
The following are the SQL Queries:
create database Test
use Test
create table UserInfo
(
UserId int identity(1,1) not null,
UserName varchar(30)not null,
UserCity varchar(30)not null,
)
insert into UserInfo values('Vishal','Mumbai'),('Rahul','Banglore'),('Muthu','Chennai')
create procedure prcGetUInfo
(
@name varchar(30)=null,
@city varchar(30)=null
)
as
begin
select * from UserInfo
where (@name is not null and @city is not null and UserName=@name and UserCity=@city) or
(@name is null and @city is not null and Usercity=@city) or
(@city is null and @name is not null and UserName=@name) or
(@name is null and @city is null and (userName in (Select UserName from UserInfo) and USERCity in(select UserCity from UserInfo)))
end
Design Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SearchForm.aspx.cs" Inherits="SearchForm" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h2>Search Records</h2>
<table>
<tr>
<td>
User Name:
</td>
<td>
<asp:TextBox ID="txtUserName" runat="server" />
</td>
</tr>
<tr>
<td>
City:
</td>
<td>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
</td>
<tr>
<td colspan="2" align="center">
<asp:Button ID="btnSubmit" runat="server" Text="Search"
onclick="btnSubmit_Click" />
</td>
</tr>
</table><br />
<asp:Label ID="lblRes" Text="Search Result" Visible="false" runat="server"></asp:Label><br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
Source Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class SearchForm : System.Web.UI.Page
{
SqlConnection con;
SqlDataAdapter da;
SqlCommand cmd;
DataSet ds;
string dbcon = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
con = new SqlConnection(dbcon);
cmd = new SqlCommand("prcGetUInfo", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", txtUserName.Text == "" ? null : txtUserName.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text == "" ? null : txtCity.Text);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
GridView1.DataSource = ds.Tables[0].DefaultView;
else
GridView1.EmptyDataText = "No Records Found";
GridView1.DataBind();
}
}