Update does not work - SQL Server Forums

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)

Update does not work

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-02-01 : 11:14:38

Have following sp. Return value is always 1 and never hits 2. Can anyone see any problems?

@UserId varchar(10),
@GroupName varchar(20),
@ApplicationName varchar(50),
@FullRights bit,
@ReadRights bit,
@WriteRights bit,
@ModifyRights bit,
@DeleteRights bit,
@Type varchar(6), 
@Return int output

as  

set nocount on

IF @Type = 'Update'
BEGIN
IF (EXISTS(SELECT 1 FROM Vecellio.dbo.AuthorityTable
WHERE  UserId = @UserId and GroupName = @GroupName and ApplicationName = @ApplicationName))
BEGIN
SET    @return = 1
UPDATE Vecellio.dbo.AuthorityTable 
SET    FullRights = @FullRights, ReadRights = @ReadRights, WriteRights = @WriteRights,
       ModifyRights = @ModifyRights, DeleteRights = @DeleteRights 
WHERE  UserId = @UserId and GroupName = @GroupName and ApplicationName = @ApplicationName
SET    @Return = 2
END
END

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 11:19:19

"Can anyone see any problems?"

Caller provides the @Return parameter as 1, and the @Type=UPDATE or the EXISTS fails, so it is left at that value.

I would pre-initialise


SET @Return = -1

just after SET NOCOUNT ON

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-02-01 : 11:43:47

I added "@Return = -1" and have following code:

SET @RETURN = -1

IF @Type = 'Update'
BEGIN
set @Return = 1
IF (EXISTS(SELECT 1 FROM Vecellio.dbo.AuthorityTable
WHERE  UserId = @UserId and GroupName = @GroupName and ApplicationName = @ApplicationName))
BEGIN
SET    @return = 2
UPDATE Vecellio.dbo.AuthorityTable 
SET    FullRights = @FullRights, ReadRights = @ReadRights, WriteRights = @WriteRights,
       ModifyRights = @ModifyRights, DeleteRights = @DeleteRights 
WHERE  UserId = @UserId and GroupName = @GroupName and ApplicationName = @ApplicationName
SET    @Return = 3
END
END

The code updates the table fine. My question is: The @Return value is 2. Why do I not get 3 when I set this value right after update?

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 12:11:51

Not possible - unless UPDATE fails perhaps? Then you would get an error message, unless you application is not showing such messages?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 12:14:59

quote:
Originally posted by Kristen

Not possible - unless UPDATE fails perhaps? Then you would get an error message, unless you application is not showing such messages?

I would also expect it to return 3 if update is success

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-01 : 14:22:11

Is SET XACT_ABORT set to ON?

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 14:29:54

I like the way you are thinking

That would still give error (I think) - at least if called direct from APP. If called from an outer-wrapper SProc then that would have to check for error, 'tis true.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources