DECLARE @empid int
DECLARE @name varchar(100)= NULL
DECLARE @panno varchar(100)=NULL
DECLARE @location VARCHAR(100)='UK'
DECLARE @SQLQuery nVARCHAR(500)=' SELECT @empid = EMPId FROM Employees '
DEClARE @WhereClause VARCHAR(500) = ''
if(@name is null and @panno is null and @location is null)
BEGIN
SELECT -1
return
END
if(@name is not null)
BEGIN
SET @WhereClause=@WhereClause+' Name='''+@name+''''
END
if(@PanNo is not null)
BEGIN
IF @WhereClause IS NULL OR @WhereClause = ''
BEGIN
SET @WhereClause=' PanNo='''+@panno+''''
END
ELSE
BEGIN
SET @WhereClause=@WhereClause+' or PanNo='''+@panno+''''
END
END
if(@location is not null)
BEGIN
IF @WhereClause IS NULL OR @WhereClause = ''
BEGIN
SET @WhereClause=' location='''+@location+''''
END
ELSE
BEGIN
SET @WhereClause=@WhereClause+' or location='''+@location+''''
END
END
IF @WhereClause IS NOT NULL AND @WhereClause <> ''
BEGIN
SET @SQLQuery = @SQLQuery + ' WHERE ' + @WhereClause
END
EXECUTE sp_executesql @SQLQuery ,N'@empid int OUTPUT',@empid OUTPUT;
select @empid