Strange Problem with mssql RAISERROR behavior

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Strange Problem with mssql RAISERROR behavior

Post by waskelton4 »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hello group,

I'm running MS SQL 2005 (SP1 i think) with PHP 4.3.7

I have a script that executes a stored procedure from PHP like so..

Code: Select all

$SQL = "EXEC dbo.saveNoteField 	@userID = ".$_SESSION['user_ID'].",
		 				@visitID = ".$_REQUEST['visit'].",
			 		        @noteID = ".$_REQUEST['nid'].",
						@noteTypeID = ".$_REQUEST['ntype'].",
						@fieldName = '".$_REQUEST['field']."', 
						@fieldData = '".$_REQUEST['data']."'";
											
		if(mssql_query($SQL)){
			 $message = explode('|', mssql_get_last_message());
		}
		else {
			$msg =0;
			$msgtext="Something is HOSED!";
		}
The 'else' is some debugging code in there.. It isn't ever run.

The sql code looks like this.

Code: Select all

SET @msgReturn = '1|this is a message!'
EXEC sp_executesql @sql
IF (@@ERROR > 0) 
	BEGIN
		SET @msgReturn = '0|Error Saving Note Field. '+ CONVERT(varchar,getDate(),0)
		GOTO finish
	END	
ELSE
	BEGIN
		IF @noteID > 0
			BEGIN
				UPDATE tblNotes SET lastEditWho = @userID, lastEditWhen = getDate(), [status] = 11 WHERE noteID = @noteID
				IF (@@ERROR > 0 OR @@ROWCOUNT = 0) 
					BEGIN
						SET @msgReturn = '0|Audit Info not saved: ' + CONVERT(varchar,getDate(),0)
						GOTO finish
					END
				ELSE
					BEGIN
						SET @msgReturn = '1|WTF MATE?'
						GOTO finish
					END
			END
		ELSE
			BEGIN 
				SET @msgReturn = '1|Field Saved: ' + CONVERT(varchar,getDate(),0)	
				GOTO finish
			END	
	END
END

finish:
BEGIN
	RAISERROR (@msgReturn, 10, 1) with log
END
GO
The crazy thing about this is that depending on the status of @noteID int the "IF @noteID > 0" portion of the code.. it works.. or it doesnt

if @noteID is not set and the IF/ELSE goes to else then the message is returned as expected.
if @noteID > 1 the message returned to PHP in the mssql_get_last_message is "Changed database context to 'myTestingDB'"
the nutty part is that the "with log" portion of the RAISERROR statement write the correct message to the log.

any thoughts on what is going on here?

Thanks
Will


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Strange Problem with mssql RAISERROR behavior - [SOLVED]

Post by waskelton4 »

Found the problem..

I had some select statements above that had values being returned that I wasn't handling and for some reason it was causing this funny behavior.

sorry about the lack of syntax declaration feyed..

ws
Post Reply