Stored procedure returning wrong value ... -1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » Stored procedure returning wrong value ... -1...

Stored procedure returning wrong value ... -1

Rate Topic

Display Mode

Topic Options

Author

Message

om-471033

Posted Wednesday, October 17, 2007 7:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 22, 2010 10:45 AM
Points: 4, Visits: 27

Let me describe wat my Stored Procedure(SP) shown below is doing before i explain the prob....

CREATE procedure usp_msg_DeleteMultipleMsg
(
@MessageList varchar(500),@mode char(10)
)
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #TempList
(
MessageID numeric
)

DECLARE @MessageID varchar(10), @Pos int

SET @MessageList = LTRIM(RTRIM(@MessageList))+ ','
SET @Pos = CHARINDEX(',', @MessageList, 1)

IF REPLACE(@MessageList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @MessageID = LTRIM(RTRIM(LEFT(@MessageList, @Pos - 1)))
IF @MessageID <> ''
BEGIN
INSERT INTO #TempList (MessageID) VALUES (CAST(@MessageID AS numeric)) --Use Appropriate conversion
END
SET @MessageList = RIGHT(@MessageList, LEN(@MessageList) - @Pos)
SET @Pos = CHARINDEX(',', @MessageList, 1)

END
END

if(@mode='sent')
update messagedetails set s_sentitems = 0
where messagedetail_pk in (select MessageID from #TempList) and s_sentitems is null

else if(@mode='read')
update messagedetails set s_read = 1
where messagedetail_pk in (select MessageID from #TempList) and s_read=0 and s_unread is null

else if(@mode='unread')
update messagedetails set s_unread = 1
where messagedetail_pk in (select MessageID from #TempList) and s_unread=0 and s_read is null

else if(@mode='r_pd')
update messagedetails set s_permanentlydeleted = 1
where messagedetail_pk in (select MessageID from #TempList) and (s_read=1 or s_unread=1)

else if(@mode='s_pd')
update messagedetails set s_sentitems = 1
where messagedetail_pk in (select MessageID from #TempList) and s_sentitems=0

if(@@error=0)
return (1)

else
return (0)

END
GO

It accepts comma seperated value(CSV) string containing a list of meggase id in @messagelist. First part of the SP parses the parameter @messagelist, picks up each message id in it and stores it in a temporary table, #templist. 2nd part of SP updates flags in my table "messagedetails" based on the parameter @mode. It is supposed to return 1 on success and 0 if there is any problem.

My problem is....it returns -1 when success and 0 on failure(which is correct). No matter what you return but it returns only -1 on success. I had this problem before in another SP which i overcame by removing the declared variables E.G. @messagelist and @pos. But it is not possible to get rid of it in this case......please help

Post #411710

Matt Miller (#4)

Posted Wednesday, October 17, 2007 7:40 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 6,998, Visits: 13,947

What are you expecting to error? Or rather - what is the error condition you're expecting to capture?

Although not necessarily the prettiest code ever - it doesn't look to me that it should routinely "fail" anywhere.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #411719

om-471033

Posted Wednesday, October 17, 2007 11:10 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 22, 2010 10:45 AM
Points: 4, Visits: 27

Thanks for the reply Matt,

I just want to return 1 if the last statement( which will be one of the statements in else-if block) is executed successfully but it returns -1 on success. Is it because of the use of variables @messagelist or @pos...?
As i have mentioned in my post earlier, I had a similar problem before in another SP which i overcame by removing all such variables(don't know why). It returns -1 on success no matter what is returned from the SP.

There is one more thing, if instead of returning any value, a print statement E.G. print 'success' is given, then it works fine.........please help....

Post #412036

« Prev Topic | Next Topic »

Permissions