If exists command returning an error

Hello, can anyone see a problem with this T-SQL?

1set ANSI_NULLSON2set QUOTED_IDENTIFIERON3GO4ALTER PROCEDURE [dbo].[Logon_P]5@.User_IDVARCHAR(50),6@.User_PasswordVARCHAR(50)7AS89IFEXISTS(SELECT 110FROM [User]11WHERE [User_Name] = @.User_ID)12BEGIN13RETURN 114IF ((SELECT User_PasswordFROM dbo.[User]WHERE [User_Name]) = @.User_ID) = @.User_Password15BEGIN16RETURN 217END18END19ELSE20RETURN 021

Its returning the following error:

Msg 4145, Level 15, State 1, Procedure Logon_P, Line 11

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

You have a RETURN in your IF..EXISTS block. SQL will return from the block as soon as it sees the RETURN. So the SELECT you have after the RETURN will not be executed. And I dont understand what you are trying to do with your second IF statement inside the first IF block..

|||

Hello my friend,

I have it working on my system. I amended it as follows: -

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[Logon_P]
(
@.User_ID VARCHAR(50),
@.User_Password VARCHAR(50)
)
AS

-- 0 = no such user
-- 1 = user exists but password incorrect
-- 2 = both user and password match
DECLARE @.Outcome AS TINYINT

IF EXISTS(SELECT 1 FROM [User] WHERE [User_Name] = @.User_ID)
BEGIN
SET @.Outcome = 1

IF EXISTS(SELECT 1 FROM [User] WHERE [User_Name] = @.User_ID AND User_Password = @.User_Password)
BEGIN
SET @.Outcome = 2
END
END
ELSE BEGIN
SET @.Outcome = 0
END

SELECT @.Outcome

In my database, I had a user of scott and a password of blue, so I tested it with the following: -

exec [Logon_P] 'scott', 'blue' -- returns 2

exec [Logon_P] 'scott', 'blue2' -- returns 1

exec [Logon_P] 'scotty', 'blue' -- returns 0

Kind regards

Scotty

|||

Thank you scotty, thats been a massive help.