T-SQL: How can i use RAISERROR for Informational Output Without Causing Script To Error?

Question

Okay so i've got a stored proc which does a bunch of things (not important).

Now, the stored proc is quite verbose, it prints out a lot of things (progress, rowcounts, etc).

As you all know, using PRINT with variables is extremely painful (requiring you to have a CAST party).

So, ive used RAISERROR to print these friendly messages (even MSDN says this is valid use, see here)

After executing the SP, i see "Query Completed With Errors", even though there is none (and the Return Code is 0 - success).

Its the RAISERROR that causing it to deem the script a failure.

Im using it like this:

RAISERROR('%s Doing Stuff....', 0, 1, @OutputTemplate) WITH NOWAIT

@OutputTemplate is just a marker for some common info (date, etc).

Notice the first parameter to RAISERROR (severity) is 0.

Okay, so i understand what im doing is silly (using an ERROR function to print INFO). But the alternative (PRINT) is painful.

What i cant understand is the Return Code is 0, so you would think SQL Server would deem this as a success.

So, the question - is there any way to use RAISERROR without causing SQL Server to deem the script as erroring? Maybe i can "reset" the error state at the end of my script (if there are no genuine errors), so the caller doesn't deem the script as fail?

EDIT:

My bad - this was a PEBKAC problem (Problem Exists Between Keyboard And Chair).

There was a tiny error in the middle of the script, didnt see it before because it outputs 1000's of lines.

Anyway, looks like you can use RAISERROR for info printing, as long as your script doesnt actually have any real errors. =)

Answer 1

PEBKAC problem.