CODEWITHSUNDEEP
sql-server-2008stored-procedures
Salam Indianoor
Salam Indianoor

Reputation: 105

Advanced Optional Parameter Searching using stored procedure in SQL

I am working on a HRM web application.Now i have to make a Attendance Report in this application. For this we have a advanced searching report form in this we have a EmployeeId, DateFrom and DateTo Fields to search date wise report. I would like to use stored procedure to search the attendence report.

The problem which i face the employeeId, DateFrom and DateTo is not compalsary. But we have to search with what the given input it may be with only employeeId, or It may with employeeId and DateFrom, Some may be employeeId, DateFrom, DateTo

I have to solve the null value when any parameter is not present when the request coming to the stored procedure hope you understood my situation...can anyone help me...

Upvotes: 1

Views: 262

Answers (1)

Mihir Shah
Mihir Shah

Reputation: 988

CREATE PROCEDURE [dbo].[USP_Employees_Search]
     @EmployeeId            Int = Null
    ,@FromDate              Date = Null
    ,@ToDate                Date = Null
AS
BEGIN

    Set Nocount On;

    Select  *
    From    dbo.Employees As e With (Nolock)
    Where   (Isnull(@EmployeeId,0) = 0 Or e.EmployeeId = @EmployeeId)
            And (@FromDate Is Null Or e.[someDateAttribute] >= @FromDate)
            And (@ToDate Is Null Or e.[someDateAttribute] <= @Todate)

END
GO

Upvotes: 1

Related Questions