Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored procedure returning wrong value ... -1 Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2007 7:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, October 17, 2007 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 PM
Points: 7,064, Visits: 15,270
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
Posted Wednesday, October 17, 2007 11:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse