Unreliable Stored Procedure behavior

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)

Unreliable Stored Procedure behavior

Author

Topic

stephensutherland
Starting Member

4 Posts

Posted - 2002-08-18 : 22:30:04

Hello Everyone;

Below I have a stored proc, that makes a poll by inserting into 3 tables. mis_Polls, mis_PollAnswer, mis_PollOptAnswer.

I'm getting unreliable behavior from this stored procedure algorithm below. I would say every 1 out of 5 polls that are created with this stored procedure ends up losing the first character of the second possible poll answer when only 2 poll answers are passed to parameter @PollAnswerDesc varChar(2000) in the format Tom:Jerry:

If Tom:Jerry: was passed, to @PollAnswerDesc , 1/5 of the time this proc would save Tom in one record and erry in another ( notice there is no J)

2 Things are consistent in this problem
-- it only happens (at this time) when I pass in 2 colon delimited values to @PollAnswerDesc.
-- it only chops of the first character of the 2nd character 1/5 of the time.

Here is the proc. Could some gurus here please assist me in debugging this proc ?

CREATE PROCEDURE spInsertPolls (
@WebSiteId Int,
@PollQuestion varchar(4000),
@IsActive Bit,
@IsDeleted Bit,
@DisplayResponse tinyint,
@DisplayStatistics Bit,
@PollAnswerDesc varChar(2000),
@PollOptAnswerDesc varChar(2000),
@PollOptOneResponse varChar(2000),
@CreatedBy Int,
@NumberofResponses Int,
@RequiresLogin Bit,
@strType Int,
@PollId Int,
@PollAnswersID varChar(2000)
) AS

DECLARE @NewPollid int,@x int, @Expression Varchar(4000), @newPollAnsId int, @OptResExpression varChar(2000),@TempRecordCount int, @minAnswerId int,@OptNoOfRecords int, @PollAnsID int,@Ans varchar(2000), @OptAns varchar(2000)

--BEGIN TRAN

Begin
--Check if the user wants to Update/insert(Legend: Update - @PollId>0; Insert - @PollID=0
if @PollId=0
Begin
Insert mis_Polls(PollQuestion,Createdby,WebSiteId,DisplayResponse,DisplayStatistics)
Values (@PollQuestion,@Createdby,@WebSiteId,@DisplayResponse,@DisplayStatistics)
Set @NewPollid=@@IDENTITY
End
else
Begin
Update mis_Polls Set PollQuestion=@PollQuestion,CreatedBy=@CreatedBy,Displayresponse=@Displayresponse,DisplayStatistics=@DisplayStatistics,isActive=1
where PollId=@PollId
Set @NewPollId=@PollId
--Set all the Ans to delete
Update mis_PollAnswer set Isdeleted=1 where PollId=@PollId
-- Set @OptNoOfRecords=(Select count(PollAnswerId) from mis_PollOptAnswer where Pollid=@pollid)
Delete mis_PollOptAnswer where PollId=@PollId
End

Set @x=1
Set @Expression=@PollAnswerDesc
Set @OptResExpression=@PollOptAnswerDesc
while @X<@NumberofResponses+1
-- Beginning of the While Loop
Begin
if (Select Patindex('%,%',@PollAnswersID))>0
Begin
Set @minAnswerId= Substring(@PollAnswersID,1,CharIndex(',',@PollAnswersID)-1)
End
Else
Begin
Set @minAnswerId= @PollAnswersID
End

if (Select Patindex('%:%',@Expression))>0
Begin
Set @Ans=Substring(@Expression,1,CharIndex(':',@Expression)-1)
End
Else
Begin
Set @Ans= @Expression
End

if (Select Patindex('%:%',@OptResExpression))>0
Begin
Set @OptAns=Substring(@OptResExpression,1,CharIndex(':',@OptResExpression)-1)
End
Else
Begin
Set @OptAns= @OptResExpression
End

if @PollId=0
Begin
Insert mis_PollAnswer(PollId, PollAnswerDesc, CreatedBy)
Values (@NewPollid,@Ans,@CreatedBy)
if len(ltrim(@OptResExpression))>0
begin
Set @newPollAnsId=@@IDENTITY
Insert mis_PollOptAnswer(PollAnswerId,PollOptAns,PollId)
Values (@newPollAnsId,@OptAns,@NewPollId)
end
End

else

Begin
if LEN(ltrim(@PollAnswersID))>0
Begin
--Get the Min(PollAnswerId) to update against that record.
--Set @minAnswerId=(Select min(PollAnswerId) From #TempAnsTable)

--THIS UPDATE STATEMENT MIGHT BELOW ALSO BE A PLACE WHERE I GET THE PROBLEM But the primary culprite is the insert statement commented below.
Update mis_PollAnswer
Set PollAnswerDesc=@Ans,
CreatedBy=@CreatedBy,isdeleted=0
where PollAnswerId=@minAnswerID
--THIS UPDATE STATEMENT MIGHT BELOW ALSO BE A PLACE WHERE I GET THE PROBLEM But the primary culprite is the insert statement commented below.
if len(ltrim(@OptResExpression))>0
Begin
Insert mis_PollOptAnswer(PollAnswerId,PollOptAns,PollId)
Values (@minAnswerId,@OptAns,@NewPollId)
End
if (Select Patindex('%,%',@PollAnswersID))>0
Begin
Set @PollAnswersID=right(@PollAnswersID,len(@PollAnswersID)-CharIndex(',',@PollAnswersID))
End
Else
Begin
Set @PollAnswersID=''
End
End
Else
Begin
--THIS INSERT STATEMENT BELOW IS ALSO A KEY LOCATION WHERE I GET THE PROBLEM
Insert mis_PollAnswer(PollId, PollAnswerDesc, CreatedBy,Isdeleted)
Values (@NewPollid,@Ans,@CreatedBy,0)
--THIS INSERT STATEMENT ABOVE IS ALSO A KEY LOCATION WHERE I GET THE PROBLEM
if len(ltrim(@OptResExpression))>0
Begin
Set @newPollAnsId=@@IDENTITY
Insert mis_PollOptAnswer(PollAnswerId,PollOptAns,PollId)
Values (@newPollAnsId,@OptAns,@NewPollId)
End

End
End
if (Select Patindex('%:%',@Expression))>0
Begin
Set @Expression=right(@Expression,len(@Expression)-CharIndex(':',@Expression))
End
Else
Begin
Set @Expression=@Expression
end
Set @OptResExpression=right(@OptResExpression,len(@OptResExpression)-CharIndex(':',@OptResExpression))

Set @x=@x+1
End
if len(ltrim(@PollOptOneResponse))>0
Begin
Insert mis_PollOptAnswer(PollOptAns,PollID)
Values(@PollOptOneResponse,@NewPollId)
End

End

stephensutherland
Starting Member

4 Posts

Posted - 2002-08-19 : 00:53:16

before someone says - i added too much code.

here is the part that works 1/5th of the time.

if (Select Patindex('%:%',@Expression))>0
Begin
Set @Ans=Substring(@Expression,1,CharIndex(':',@Expression)-1)
End
Else
Begin
Set @Ans= @Expression
End

sometimes the result of @Ans
might be jerry or erry when @Expression holds the value of tom:jerry

stev

stephensutherland
Starting Member

4 Posts

Posted - 2002-08-19 : 01:10:54

woops sorry i wasted everyone's time i figured out the problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources