Set NoCount On

Although this chapter assumes you are already familiar with stored procedures, there is one statement that doesn’t get much attention. I think it is very important to be aware of it.

SET NOCOUNT ON

The Set NoCount On statement prevents extraneous messages from being output by SQL Server. By default, SQL Server outputs status messages while it executes a stored procedure. These often state how many records were affected by the last statement. Unfortunately, these status messages confuse Crystal Reports and it thinks that they are part of the recordset. This obviously isn’t the case and the report won’t generate any output.

If you are working with simple stored procedures, the majority of them consist of a SELECT statement followed by a list of tables, fields and a join method. Crystal Reports handles this type of stored procedure fine. Once you start getting into writing more complex stored procedures you will find that you often need to execute multiple SQL statements within one stored procedure.

‘SET NOCOUNT ON
INSERT INTO AuditLog …
SELECT * FROM tblSales WHERE …

In the above code, the SET NOCOUNT ON statement is commented out. Running this code will generate two output messages for each statement. They will be in the format of xx records affected. This message is passed prior to the records being returned from the stored procedure. This conflicts with what the report is expecting. Thus, it doesn’t use the data from the SELECT statement as the recordset. By removing the comment from the first line, you tell the database server not to report how many records are affected. This eliminates Crystal Reports from incorrectly using these messages as part of the database. Ideally, this statement would be the first statement in every stored procedure.

If you use temp tables in a stored procedure, always use SET NOCOUNT ON. This is because temp tables always generate extraneous messages as the records are generated and saved to the temporary table.