PRINT statement in T-SQL

Question

Why does the PRINT statement in T-SQL seem to work only occasionally? What are the restrictions on its use? It seems that if a result set is created, it becomes a null function, I suppose this prevents damage to the result set, but can it be displayed in a different result set, for example in the line counter?

Answer 1

So, if you have something like the following, you say that you have no print result?

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

If you use SQL Query Analyzer, you will see that there are two tabs at the bottom, one of which is “Messages” and that “print” statements will be displayed there.
If you are worried about timing for viewing print statements, you can try using something like

  raiserror ('My Print Statement', 10.1) with nowait 

This will give you a message as soon as the statement is created, rather than buffering the output, since Query Analyzer will work under most conditions.

Answer 2

A TSQL print statement is a misunderstood creature, possibly because of its name. It actually sends the message to the error / message processing engine, which then passes it to the calling application. PRINT is pretty dumb. You can send only 8000 characters (4000 Unicode characters). 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 only 2044 characters. However, it’s 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 C. C standard library. RAISERROR can also indicate the error number, severity and status code in addition to the text message, and can also be used to return custom messages created using the sp_addmessage system stored procedure. You can also force message logging.

Your error handling procedures will not be good for receiving messages, even though the messages and errors are so similar. Of course, this method depends on how you connect to the database (OLBC, OLEDB, etc.). In order to receive and process messages from the SQL Server Database Engine, when you use System.Data.SQLClient, you need to create a SqlInfoMessageEventHandler delegate that identifies the method that processes the event to listen to the InfoMessage event in the SqlConnection class, you will find that the message context information, such as severity and status, are passed as arguments to the callback because, from a system perspective, these messages are like errors.

It is always useful to have a way to receive these messages in your application, even if you simply buffer to a file, because it will always be useful for them when you try to pursue a really incomprehensible problem. However, I cannot think that Id want end users to ever see them if you cannot reserve an information layer displaying the material in the application.

Answer 3

Query Analyzer buffers messages. The PRINT and RAISERROR statements use this buffer, but the RAISERROR statement has the WITH NOWAIT parameter. 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 syntax similar to the printf function for C to format text.

Note that using RAISERROR with the WITH NOWAIT option will clear the message buffer, so all previously buffered information will also be displayed.

Answer 4

I recently ran into this and it ended up having a conversion operator for a null variable. Since this caused errors, the entire print statement was rendering as null, and not printing at all.

Example. This will not be done.

 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') 

Answer 5

It’s in the interest of anyone reading this question that really skips printing instructions from their output, in fact, there are times when printing is done but not returned to the client. I can’t say exactly what it is. I can tell you that if you put the go statement immediately before and after any print statement, you will see it if it is executed.

Answer 6

Do you have variables associated with these print operations? if so, I found that if the variable does not matter, then the print statement will not exit.

Answer 7

I just happened. My problem was that I had a date variable inside my string variable. When I went to print the line, it did not print or display an error message.

I essentially passed the date variable to a string in my string variable. Then it prints great!