SQL Tip - Stored Procedures do not Return Display fields

Applies to Infiniti v8.0 or later
 

If your stored procedure is not returning columns as display fields, it is possible that it is returning the first row count rather than the final result.

This can be overcome by adding a SET NOCOUNT ON line to the start of the Stored Procedure.

For example without the SET NOCOUNT ON line the stored procedure below will return the rows affected by the INSERT statement rather than the resulting SELECT statement.

/***** Object: StoredProcedure [dbo].[sp_CreateRecord] Script Date: 03/29/2011 13:47:23 *****/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_CreateRecord]

--Input params

@Title varchar(100)

AS

BEGIN

--Next Line is necessary - Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

AnchorSET NOCOUNT ON

--insert the new record

INSERT INTO Records (Author, dateCreated )

VALUES (@Title, getdate())

--return the entire record created by the stored proc

select [id]

,Author

,dateCreated

from Records where ID = @@IDENTITY

END

GO

Keywords

 
sql datasource data source mssql mysql transact