Stored procedure IFELSE block not entering the ELSE block

user_19586836 0
Viewed 38

I have a table called Employee_Info with Employee_ID,Name,Age and Address columns.I want my query to return the EmployeeID if a particular name exists, if doesnt exist then it has to return another select condition where it returns all rows with null Address values. But my query is not jumping to the else block but instead showing a table with empty columns of the IF block itself. And i have to be using the concept of Stored Procedures for this This is my code:

ALTER PROC spEmployee
@Emp_Name VARCHAR(50)
AS
BEGIN
  IF @Emp_Name =@Emp_Name
     SELECT Emp_ID FROM EMPLOYEE_INFO WHERE Emp_Name=@Emp_Name
  ELSE 
     SELECT * FROM EMPLOYEE_INFO WHERE Address IS NULL
 END
if-statement ssms stored-procedures sql-server database
1 Answers

EXISTS operator can be used to check whether the particular record is existing in the table. Here we can use IF condition with EXISTS operator and get the result we expect.

Please find the below answer.

ALTER PROC spEmployee
@Emp_Name VARCHAR(50)
AS
BEGIN

IF EXISTS (SELECT 1 FROM EMPLOYEE_INFO WHERE Emp_Name=@Emp_Name)
    SELECT Emp_ID FROM EMPLOYEE_INFO WHERE Emp_Name=@Emp_Name;
ELSE
    SELECT * FROM EMPLOYEE_INFO WHERE Address IS NULL;

END;
user_124674840
Related