I Have 3 Input Parameters,Based On Values Of Each Input Para

Feb 14 2014 5:02 AM


I Have 3 Input Parameters,Based On Values Of Each Input Parameter I Have To Prepare The Sql In Procedure Dynamically ..in that i got error .emp id null values are passed



 
create procedure  sri_sp

@Name VARCHAR(100), 
@Status VARCHAR(100), 
@PanNo VARCHAR(100), 
@DateOfLeaving VARCHAR(100), 
@Designation VARCHAR(100),
@NoOfDays VARCHAR(100),
@BankName VARCHAR(100),
@BankBranchName VARCHAR(100),
@AccountNo VARCHAR(100), 
@EPFNo VARCHAR(100),
@Basic VARCHAR(100),
@HRA VARCHAR(100),
@Conveyance VARCHAR(100), 
@Medical VARCHAR(100), 
@OtherAllowance VARCHAR(100), 
@SpecialAllowance VARCHAR(100), 
@incentive VARCHAR(100), 
@bonus VARCHAR(100), 
@Arrears VARCHAR(100), 

begin  

DECLARE @empid
DECLARE @Name varchar(100)

DECLARE @panno varchar(100)
DECLARE @location VARCHAR(100)



DECLARE @SQLQuery VARCHAR(500)='SELECT 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+' and 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+' and 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


DECLARE @EMPSALRYID INT=(SELECT COUNT(EmpId) FROM Employees WHERE EmpId=@empid)

IF(@empid=0 or @empid=null)
BEGIN
-- INSERT EMPLOYEE DETAILS
INSERT INTO Employees(Name,PanNo, BankName, BankBranchName, AccountNo,Location,Status,Designation, EPFNo,DateOfLeaving, ESI)
VALUES(@Name,@PanNo,@BankName, @BankBranchName, @AccountNo,@Location,Convert(char,@Status),@Designation, @EPFNo, CONVERT(date,@DateOfLeaving,3),convert(bit,@ESI))
Set @empid=(select @@IDENTITY)
END
ELSE
BEGIN
--UPDATE EMPLOYEE DETAILS
UPDATE Employees
SET Name=@Name,
PanNo=@PanNo,
BankName=@BankName,
BankBranchName=@BankBranchName,
Location=@Location,
Status=convert(char,@Status),
Designation=@Designation
,EPFNo=@EPFNo,
DateOfLeaving= CONVERT(date,@DateOfLeaving,3),
ESI=Convert(bit,@ESI)
WHERE EmpId=@empid
END