PRINT statement in T-SQL

Question

Why does the PRINT statement in T-SQL seem to only sometimes work? What are the constraints on using it? It seems sometimes if a result set is generated, it becomes a null function, I assumed to prevent corrupting the resultset, but could it's output not go out in another result set, such as the row count?

Solution 1

So, if you have a statement something like the following, you're saying that you get no 'print' result?

select * from sysobjects
PRINT 'Just selected * from sysobjects'

If you're using SQL Query Analyzer, you'll see that there are two tabs down at the bottom, one of which is "Messages" and that's where the 'print' statements will show up.
If you're concerned about the timing of seeing the print statements, you may want to try using something like

raiserror ('My Print Statement', 10,1) with nowait

This will give you the message immediately as the statement is reached, rather than buffering the output, as the Query Analyzer will do under most conditions.

Solution 2

The Print statement in TSQL is a misunderstood creature, probably because of its name. It actually sends a message to the error/message-handling mechanism that then transfers it to the calling application. PRINT is pretty dumb. You can only send 8000 characters (4000 unicode chars). You can send a literal string, a string variable (varchar or char) or a string expression. If you use RAISERROR, then you are limited to a string of just 2,044 characters. However, it is much easier to use it to send information to the calling application since it calls a formatting function similar to the old printf in the standard C library. RAISERROR can also specify an error number, a severity, and a state code in addition to the text message, and it can also be used to return user-defined messages created using the sp_addmessage system stored procedure. You can also force the messages to be logged.

Your error-handling routines won’t be any good for receiving messages, despite messages and errors being so similar. The technique varies, of course, according to the actual way you connect to the database (OLBC, OLEDB etc). In order to receive and deal with messages from the SQL Server Database Engine, when you’re using System.Data.SQLClient, you’ll need to create a SqlInfoMessageEventHandler delegate, identifying the method that handles the event, to listen for the InfoMessage event on the SqlConnection class. You’ll find that message-context information such as severity and state are passed as arguments to the callback, because from the system perspective, these messages are just like errors.

It is always a good idea to have a way of getting these messages in your application, even if you are just spooling to a file, because there is always going to be a use for them when you are trying to chase a really obscure problem. However, I can’t think I’d want the end users to ever see them unless you can reserve an informational level that displays stuff in the application.

Solution 3

Query Analyzer buffers messages. The PRINT and RAISERROR statements both use this buffer, but the RAISERROR statement has a WITH NOWAIT option. To print a message immediately use the following:

RAISERROR ('Your message', 0, 1) WITH NOWAIT

RAISERROR will only display 400 characters of your message and uses a syntax similar to the C printf function for formatting text.

Please note that the use of RAISERROR with the WITH NOWAIT option will flush the message buffer, so all previously buffered information will be output also.

Solution 4

I recently ran into this, and it ended up being because I had a convert statement on a null variable. Since that was causing errors, the entire print statement was rendering as null, and not printing at all.

Example - This will fail:

declare @myID int=null
print 'First Statement: ' + convert(varchar(4), @myID)

Example - This will print:

declare @myID int=null
print 'Second Statement: ' +  coalesce(Convert(varchar(4), @myID),'@myID is null')

Solution 5

For the benefit of anyone else reading this question that really is missing print statements from their output, there actually are cases where the print executes but is not returned to the client. I can't tell you specifically what they are. I can tell you that if you put a go statement immediately before and after any print statement, you will see if it is executed.

View more solutions

Comments

[Maurício]

Could you give an example of some code that sometimes prints, and sometimes doesn't. I don't think I understand the question.

[David T. Macknet]

Cool deal. Glad this worked out for you. I usually tell query analyzer to give me text output, so that I can see the messages inline (Ctrl+T). Enjoy!

[Chris Simpson]

I have also experienced cases where a print statement does into write to the message window when expected. It happens to me after a commit or a rollback.

[Cobusve]

RAISERROR syntax was a pain, but putting an empty one after my PRINT did the job, thanks for the info!

[binki]

You sound like you don’t understand the effect of concatenating NULL with a string. PRINT 'a' + CAST(NULL AS VARCHAR(MAX)); behaves exactly as I’d expect it to.

[troy]

Not just PRINT: using the + operator with any null value anywhere in T-SQL results in a null, regardless if there were other things to be concatenated together that weren't null. Using ISNULL(@myID, '') or COALESCE as you have will allow the null to be able to concatenated with strings.

[deniz]

What are the 10 and 1 arguments in the raiserror example?

[David T. Macknet]

The 10, 1 are really just saying "nothing to see here" to the server. See docs.microsoft.com/en-us/sql/t-sql/language-elements/… for details on what they actually mean.

[FMFF]

When used this way, is RaiseError identical to PRINT with the exception of timing?

[David T. Macknet]

I believe so, yes. It may have the added benefit of outputting the buffer immediately, so other print statements would also be displayed, but that's something you'd want to play with to verify. My understanding is that it triggers the queue to be output immediately.

[Matt Kemp]

Adding GO statements flushed my PRINTs to the shell which is what I was after