T-SQL error if

Question

I am trying to execute the following SQL 2008 code, it says that there is a problem next to "=" and "else" ... I cannot understand what is wrong with the code

ALTER PROCEDURE dbo.LoginEmp @username NVARCHAR(10), @password NVARCHAR(10), @confirm INT output, @emp_name NVARCHAR(50) output, @emp_id BIGINT output AS IF EXISTS (SELECT @emp_id = emp_id, @emp_name = emp_name_ara FROM Employee WHERE ( emp_username LIKE @username AND emp_password LIKE @password )) BEGIN SET @confirm=1 INSERT INTO EmployeeLog (log_emp_id, log_act_id, log_date, log_data) VALUES (@emp_id, 1, GETDATE(), -1) END ELSE BEGIN SET @confirm=0 END RETURN 

Answer 1

Instead of trying to assign the output parameters inside EXISTS to do the assignment, then check @@rowcount to see if matching matches were found.

 ALTER PROCEDURE dbo.LoginEmp @username NVARCHAR(10), @password NVARCHAR(10), @confirm INT output, @emp_name NVARCHAR(50) output, @emp_id BIGINT output AS SELECT @emp_id = emp_id, @emp_name = emp_name_ara FROM Employee WHERE ( emp_username = @username AND emp_password = @password ) IF @@ROWCOUNT = 1 BEGIN SET @confirm=1 INSERT INTO EmployeeLog (log_emp_id, log_act_id, log_date, log_data) VALUES (@emp_id, 1, GETDATE(), -1) END ELSE BEGIN SET @confirm=0 END 

Answer 2

There is a statement check if any row is present in the result set returned by the query. In your example, this simply assigns values ​​to a variable. For more information, please check this link: Assigning a variable inside an IF EXISTS clause

Answer 3

Can you move IF EXISTS after BEGIN

Answer 4

Try the following:

 ALTER PROCEDURE dbo.LoginEmp @username nvarchar(10), @password nvarchar(10), @confirm int output, @emp_name nvarchar(50) output, @emp_id bigint output AS Begin if exists (SELECT emp_id, emp_name_ara FROM Employee WHERE (emp_username LIKE @username AND emp_password LIKE @password)) begin // Here Retrieve the Data into Variable again. Like You did Above Before. set @confirm=1 INSERT INTO EmployeeLog (log_emp_id,log_act_id,log_date,log_data) VALUES (@emp_id,1,GETDATE(),-1) end else begin set @confirm=0 end end RETURN 

Answer 5

Edit

 SELECT @emp_id = emp_id, @emp_name = emp_name_ara 

to

 SELECT emp_id, emp_name_ara 

Add @emp_id and @emp_name variables to the WHERE clause

 IF EXISTS (SELECT emp_id, emp_name_ara FROM Employee WHERE ( emp_username LIKE @username AND emp_password LIKE @password AND emp_id=@emp _id AND emp_name_ara = @emp_name ))