Checking the status of a stored procedure (sql server 2005)

Question

I have an SP where I need to check if the condition is met

ALTER PROCEDURE [dbo].[spCheck] @strEmpname VARCHAR(50), @intReturn INT OUTPUT, @intWorkdID INT, @intEmpID INT AS BEGIN IF(@intWorkdID is not null and @intWorkdID != '') BEGIN IF EXISTS ( SELECT * FROM Employee WHERE [Empname] = @strEmpname AND WorkID = @intWorkdID ) SELECT @intReturn = '1' END ELSE IF(@intEmpID is not null and @intEmpID != '') BEGIN IF EXISTS ( SELECT * FROM Employee WHERE [Empname] = @strEmpname AND PeopleID = @intEmpID ) SELECT @intReturn = '1' END ELSE IF(@intEmpID is not null and @intEmpID != '') and(@intWorkdID is not null and @intWorkdID != '') BEGIN SELECT @intReturn = '0' END END 

here based on WorkID, EmpID
1 condition and condition 2 must fulfill

if both conditions do not work, I need to fix the third condition

can anyone tell the syntax for it

thanks

Prince

Answer 1

The best way is to use

Try something as shown below:

 SELECT @intReturn = CASE WHEN @intWorkdID IS NULL THEN 1 WHEN @intWorkdID<>'' THEN 1 WHEN @intEmpID IS NULL THEN 1 WHEN @intEmpID <>'' THEN 1 ELSE 0 END 

Case ... When

for this

Answer 2

An int cannot equal. ''

I'm not sure what you are asking in your logic, but when if does not match, it starts the else part. After that, you can specify if -> else after that according to your script.