OPTIONAL PARAMETERS IN SQL USING STORED STORED PROCEDURE

In this article i am going to tell you how to create stored procedure with optional parameters

STEP 1: - CREATER TABLE

CREATE TABLE tblEmployees
(
Id  int primary key identity,
EmpName   varchar(50),
Gender    varchar(50),
Salary    int
)

STEP2:- INSERT VALUES INTO TABLE

        INSERT INTO tblEmployees VALUES('mark','male',15000)
        INSERT INTO tblEmployees VALUES('mike','male',20000)
        INSERT INTO tblEmployees VALUES('mary','female',25000)
        INSERT INTO tblEmployees VALUES('david','male',35000)

STEP 3:- CREATE STORED PROCEDURE

CREATE PROCEDURE USP_SearchEmployees
(
@EmpName  varchar(50)=NULL,
@Gender   varchar(50)=NULL,
@Salary   int=NULL
)
AS
BEGIN
SELECT *FROM tblEmployees
WHERE (EmpName=@EmpName OR @EmpName IS NULL) AND
      (Gender=@Gender OR @Gender IS NULL) AND
      (Salary=@Salary OR @Salary IS NULL)
END

STEP 4:- EXECUTE STORED PROCEDURE

EXECUTE USP_SearchEmployees 'mary'