T-SQL if statement error

Question

i'm trying to execute the following SQL 2008 code it says there is a problem near "=" and "else"... i cant 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

if the syntax is like in oracle, there is missing "then" and "endif" — desperateCoder, May 16 '13 at 11:52
@MartinSmith Is there any tool to format the SQL code like above ? If yes can you pls provide the link. — Santosh, May 16 '13 at 11:53
You won't be able to assign a variable inside the `EXISTS(SELECT`. @Santosh I use SQL Pretty Printer. An online version is here http://www.dpriver.com/pp/sqlformat.htm — Martin Smith, May 16 '13 at 11:54
On a side note, shouldn't username an password tests use = instead if LIKE ? — Marcello Romani, May 16 '13 at 11:54
@MartinSmith Why i cant assign a value to a variable ito IF EXISTS ? — Monir Tarabishi, May 16 '13 at 12:00
@MarcelloRomani My friend its a nvarchar field so its better if i use LIKE operator — Monir Tarabishi, May 16 '13 at 12:01
@iceDragon - A statement that assigns to variables does not return any result set so it doesn't make sense inside an `EXISTS` — Martin Smith, May 16 '13 at 12:06
IMHO authenticating via LIKE comparison is a MAJOR security hole. Plus, I think SQL Server has an Unicode-aware = operator http://msdn.microsoft.com/en-us/library/ms189863.aspx — Marcello Romani, May 16 '13 at 13:12
@MarcelloRomani is correct. If you know a username then you could just enter `%` as the password to authenticate with `LIKE`. It should use `=` (and also passwords should be hashed anyway in most cases) — Martin Smith, May 16 '13 at 15:30

Martin Smith · Accepted Answer · 2013-05-16T15:30:22.437

Instead of trying to assign to the output parameters inside the EXISTS do the assignment then check @@rowcount to see if any matching row(s) 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

@iceDragon - You may well want to use `IF @@ROWCOUNT = 1` instead of `IF @@ROWCOUNT > 0` I just used the second one to preserve as far as possible what the original semantics with `EXISTS` would have been. — Martin Smith, May 16 '13 at 12:10

score 1 · Answer 2 · edited May 23 '17 at 10:25

1

Exists operator checks if any row present in resultset returned by query. In your example it's just assigning values to variable. For more reference please check this link : Assigning a variable inside an IF EXISTS clause

edited May 23 '17 at 10:25

Community

1
1

answered May 16 '13 at 12:05

Rohan

2,970
1
18
26

score 0 · Answer 3 · answered May 16 '13 at 11:52

0

Can you move IF EXISTS after BEGIN

answered May 16 '13 at 11:52

Semih Yagcioglu

3,733
1
23
42

score 0 · Answer 4 · answered May 16 '13 at 11:53

Try This :

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

it doesn't affect the result :S – Monir Tarabishi May 16 '13 at 12:03 — Monir Tarabishi, May 16 '13 at 12:03

score 0 · Answer 5 · answered May 16 '13 at 11:55

0

Change

SELECT @emp_id = emp_id,
                      @emp_name = emp_name_ara

to

SELECT emp_id, emp_name_ara

Append @emp_id and @emp_name variables to 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  ))

answered May 16 '13 at 11:55

Mudassir Hasan

26,105
18
90
124

but i need to assign those values to the output variables – Monir Tarabishi May 16 '13 at 12:04
i am not using the variables to test a value, i need a return value through them – Monir Tarabishi May 16 '13 at 12:13

T-SQL if statement error

5 Answers5