Probably just another newbie question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Probably just another newbie question

Author

Topic

dannylister
Starting Member

9 Posts

Posted - 2007-12-20 : 13:55:18

Hi everyone,

Trying my hand at some simple user validation stored procedures and I've ran into a bit of a problem. Depending on whether the users details passed to the proc are valid or not, I want to return a 0 or 1 depending upon the validation result. I can manage to do this so far. BUT... I wish to update the last login date of the user upon a successful validation. So far, my procedure looks like this:

CREATE PROCEDURE [dbo].[UsersValidate]
	@Email varchar(250),
	@Password varchar (250)
AS
BEGIN
	-- If password is correct
	IF EXISTS
		(SELECT 
				*
			FROM 
				Users
			LEFT JOIN
				UserGroups
			ON
				Users.UserGroupID = UserGroups.ID
			WHERE 
				Users.Email = @Email AND 
				Users.Password = @Password AND
				Users.Enabled = 1 AND
				UserGroups.Enabled = 1)
		-- Update last login date
		UPDATE 
			Users
		SET 
			Users.DateLastLogin = getDate()
		WHERE 
			Users.Email = @Email
		-- Return true
		SELECT 1
	ELSE
		-- Return false
		SELECT 0
END

As you can imagine I get an error. The error message I get is 'Incorrect syntax near the keyword 'ELSE'.' Can somebody help me please? Thanks, from Danny.

http://www.dannylister.co.uk/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 13:57:42

Which error?

E 12°55'05.25"
N 56°04'39.16"

dannylister
Starting Member

9 Posts

Posted - 2007-12-20 : 13:58:38

I've just updated the above post.

'Incorrect syntax near the keyword 'ELSE'.' is the error.

http://www.dannylister.co.uk/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 14:01:32

[code]CREATE PROCEDURE dbo.UsersValidate
(
@Email VARCHAR(250),
@Password VARCHAR(250)
)
AS

SET NOCOUNT ON

IF EXISTS (
SELECT *
FROM Users AS u
INNER JOIN UserGroups AS ug ON ug.ID = u.UserGroupID
WHERE u.Email = @Email
AND u.Password = @Password
AND u.Enabled = 1
AND ug.Enabled = 1
)
BEGIN
UPDATE Users
SET DateLastLogin = GETDATE()
WHERE Email = @Email

SELECT 1
END
ELSE
SELECT 0[/code]

E 12°55'05.25"
N 56°04'39.16"

dannylister
Starting Member

9 Posts

Posted - 2007-12-20 : 14:03:44

Ah! Thank you for your help. The 'Begin' and 'End' tags worked a treat :)

http://www.dannylister.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources