store procedure not setting message

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

SQL Server Development (2000)

store procedure not setting message

Author

Topic

awalia
Starting Member

1 Post

Posted - 2005-12-11 : 19:41:15

hi all i am having problem with store procedure i am updating a table
and sending a success message but after executing the code the SP is not returning the value there is no error but i don't understant why its not setting the message plz help i have post the code also.Moreover the update query has updated the table but don't set message if i comment the update query then it work fine.

CREATE PROCEDURE [aims].[checkLostStatus1] @rcode varchar(20) ,@maskrcode varchar(20) ,@status varchar(20), @onshelf varchar(20), @resnr int,
@message varchar(300) OUT
AS

DECLARE @classcount as int;
DECLARE @count as int;
DECLARE @part_of_class_set as varchar(3);
SET @count=0;
DECLARE @cname as varchar(30);
DECLARE @sname as varchar(30);
DECLARE @class as varchar(30);
DECLARE @msg as varchar(300);
DECLARE @err as int;
DECLARE @errcount as int;

DECLARE @returnval int;

set @message='Reach here';

select @classcount= count(*) from aims.class_sets where aims.class_sets.copy_r_code = @rcode;
select @err=@@ERROR;
if(@err<>0)
begin
set @message='Error ';
end;

if(@classcount>0)
set @part_of_class_set = 'Y';
else
set @part_of_class_set = 'N';

if(@part_of_class_set = 'Y')
begin
select @count= count(*) from aims.class_sets where aims.class_sets.copy_r_code = @rcode and aims.class_sets.status = @status;
select @err=@@ERROR;
if(@err<>0)
begin
set @message=@message+'Error 2';
end;
end;

if(@count>0) --resource has been lost
BEGIN
begin tran
update aims.loan set aims.loan.ret_date = getdate() , aims.loan.odu_flag = 'F' where aims.loan.odu_flag = 'L' and aims.loan.r_code = @rcode;
if(@@ERROR <> 0)
set @message='error is here';
else
set @message='success';

commit;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-11 : 20:07:55

awalia Welcome to SQLTeam.

How do you call your stored procedure ?
Is it

exec checkLostStatus1 @rcode, @maskrcode, @status, @onshelf, @resnr, @message OUTPUT

-----------------
[KH]
Where Am I ?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-11 : 23:07:48

If the message is an error message, are you wanting to return an error code like you would with RAISERROR? Look it up in Books Online and see if that's what you are trying to achieve.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-12 : 00:57:48

>>Moreover the update query has updated the table but don't set message if i comment the update query then it work fine

Is @message assigned wrong message?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-12-12 : 02:07:10

"exec checkLostStatus1 @rcode, @maskrcode, @status, @onshelf, @resnr, @message OUTPUT"

Note that @message is current required so you would have to do

DECLARE @myString varchar(300)
exec checkLostStatus1 @rcode, @maskrcode, @status, @onshelf, @resnr, @message=@MyString OUTPUT
SELECT @myString

You are also missing a matching END after:


if(@count>0) --resource has been lost 
BEGIN

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources