Beginner - If-@@rowcount-Raiserror problem

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

General SQL Server Forums

New to SQL Server Programming

Beginner - If-@@rowcount-Raiserror problem

Author

Topic

columbo1977
Starting Member

4 Posts

Posted - 2013-10-20 : 19:17:29

Hi There

for a uni tutorial I am trying to add 10 marks to all module 1 marks but fail if the mark is over 100..... haveing trouble as it has been a long time since I did SQL and I am very very rusty.

Can anyone give me any clues, I am supposed to look up IF, @@ROWCOUNT and RAISERROR but I have been searching for examples etc for ages and I just dont know what to do and this is stopping me moving forward now.

What I have so far is :


-- ----------------------------------------------------------------
-- Tutorial 4 - q1 
Use Assessment
Go
begin tran error
begin try
	If moduleCode = 1 and mark < 100 Then
	Select 'BEFORE', moduleCode,mark
	From AssessSchema.Result
	where moduleCode = 1
	begin tran MarksUpdateBy10
	Update AssessSchema.Result
	set mark = mark+10
	where moduleCode = 1
	commit tran
end try
begin catch
	rollback tran
end catch
Else Goto End
:End
End If
select 'AFTER' , *
from AssessSchema.Result
Go

Cheers

Graham

columbo1977
Starting Member

4 Posts

Posted - 2013-10-21 : 02:16:41

Oh, forgot to say. I have to update the mark of module 1 by 10 but it needs to fail if the mark goes above 100.

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 06:41:22

something like this


Update AssessSchema.Result
set mark = mark+10
where moduleCode = 1
AND   mark+10 <= 100

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

columbo1977
Starting Member

4 Posts

Posted - 2013-10-21 : 16:14:31

I already got something like that, and it worked. They want us to use the @@rowcount, IF and RAISERROR

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 08:09:57

quote:
Originally posted by columbo1977

I already got something like that, and it worked. They want us to use the @@rowcount, IF and RAISERROR

G

do you mean this?


DECLARE @ErrNo int,@Cnt int
Update AssessSchema.Result
set mark = mark+10
where moduleCode = 1
AND   mark+10 <= 100
SELECT @Cnt=@@ROWCOUNT,
@ErrNo=@@ERROR

 IF @ErrNo > 0 
 BEGIN
     PRINT 'your error message'
     RETURN (@Err)
 END
 ELSE
    RETURN (@Cnt)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

columbo1977
Starting Member

4 Posts

Posted - 2013-10-22 : 08:41:41

Thanks for the assisstance so far :)

Is RAISERROR a seperate command as that needs to be in there too.

I'm new (again) to this, why is @Cnt in there ?

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 09:05:18

quote:
Originally posted by columbo1977

Thanks for the assisstance so far :)

Is RAISERROR a seperate command as that needs to be in there too.

I'm new (again) to this, why is @Cnt in there ?

G

RAISERROR is required in case you want to raise the error from sql code to calling application directly
In method I showed above you just capture errorid as return value and do eroor notification at application end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources