Sqlserver : Search query

You want to create a report (Search)

which has different filters :


Based on the filters u have to generate the report.


User can select all filters or none of the filters and can click on Search button to generate the button


Ur Stored Procedure should have that many input parameters (similar to filters) for getting the recordset.

Input parameters should accept null values


   @FirstName varchar(25) = null,
   @LastName varchar(25) = null,
   @Title varchar(25) = null
      SELECT ID, FirstName, LastName, Title
      FROM tblUsers
        (@FirstName IS NULL OR FirstName = @FirstName) AND
        (@LastNameName IS NULL OR LastName = @LastName) AND
        (@Title IS NULL OR Title = @Title)



Second method


    (FirstName = ISNULL(@FirstName, FirstName)
    OR COALESCE(@FirstName, FirstName, '') = '')
AND (LastName = ISNULL(@LastName, LastName)
    OR COALESCE(@LastName, LastName, '') = '')
AND (Title = ISNULL(@Title, Title)
    OR COALESCE(@Title, Title, '') = '')

second method:




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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: