Conditional Update then Select query

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 2000 Forums

Transact-SQL (2000)

Conditional Update then Select query

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2005-12-31 : 02:06:17

Hopefully, someone can point out whatever I'm missing here. I have a stored procedure which I need to update some user information if its their first time logging in, but I can't seem to get my updates to fire.

Here is my procedure
CREATE PROCEDURE AppSec_UserAuth
(
@WinID varchar(50),
@UID int OUTPUT,
@URole nvarchar(15) OUTPUT,
@LastLogin datetime OUTPUT,
@LastImg int OUTPUT,
@DisplayName nvarchar(50) OUTPUT
)
AS

set nocount on

BEGIN
IF NOT EXISTS (select * from tbl_Users WHERE usrWinAcct = @WinID)
goto no_rec
ELSE
checkuser:
BEGIN
IF EXISTS ((SELECT UserID FROM tbl_Users WHERE (LastLogin IS NULL) AND (UserID = @UID)))
BEGIN
begin TRAN upd_new
UPDATE tbl_Users SET LastImg = 1, LastLogin = CONVERT(CHAR(8),GETDATE(),10) WHERE (UserID = @UID)
COMMIT TRAN upd_new
goto checkuser
END
ELSE
BEGIN
SELECT @LastLogin = (Select LastLogin FROM tbl_users WHERE UserID = @UID)
IF GetDate() > @LastLogin
BEGIN
begin tran upd_user
UPDATE tbl_Users SET LastLogin = CONVERT(CHAR(8),GETDATE(),10) WHERE (UserID = @UID)
commit tran upd_user
goto checkuser
END
ELSE
BEGIN
SELECT
@UID = u.UserID,
@DisplayName = u.usrDisplayName,
@URole = sec.AppSecAbbr,
@LastLogin = u.LastLogin,
@LastImg = u.LastImg

FROM
tbl_Users u INNER JOIN
tbl_AppSec sec ON u.AppSecID = sec.AppSecID

WHERE
(u.usrWinAcct = @WinID)
return
END
END
END
END

no_rec:
begin
select @DisplayName = 'User does not exists.'
return
end
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-31 : 11:37:40

How do user records get into the tbl_users table? If the user doesn't exist then there is no path in your logic that deals with that. Since we don't know your business logic it's hard to say what the problem is. I suggest you put in some PRINT statements at each logic path so you can see what flow the code is taking at runtime.

on a side note, the explicit BEGIN/COMMIT tran statements are not needed here. Because it is only a single statement per transaction and you're not doing any kind of data check/rollback so they are not necessary.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources