RETURN statement performance?

[spDavCom]

QUESTION:

I've heard that using the RETURN statement can boost performance because SQL Server will not create a recordset.

Where does the return statement go in my stored proc?

NOTE:
I'm using asp.net to call my stored procs.

Any input or syntax is much appreciated in advance. Thank you.

 

[JohnDTampaBay]

The RETURN statement should be the last statement in the stored procedure or at whatever point you would like to RETURN from the procedure. I use it in error handling to bail out of a procedure early and on inserts to RETURN the identity created by the insert. Something like this:

CREATE PROC usp_DoIt
  @NameToInsert varchar(20) NULL
AS BEGIN
  SET NOCOUNT ON

  IF @NameToInsert IS NULL BEGIN
     RaisError('Invalid name',16,1)
     RETURN -1    --There is an error so bail out
  END

  INSERT Names (TheName) VALUES (@NameToInsert)

  RETURN @@Identity    --ID of the inserted row
END

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[tlbroadbent]

Return can only return a single integer value. Thus it is of limited value for most applications that will return a set of records or character strings. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm