October 17, 2007 at 7:22 am
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
October 17, 2007 at 7:40 am
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?
October 17, 2007 at 11:10 pm
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....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply