Stored Procedure Return Value

[stevensteven]

Is it possible to surpress a stored procedure's return value?

Steven

 

[SQLSister]

You mean where it tells you the number of records processed?

Loook at Set NoCount On in Books online.

 

[stevensteven]

I was looking for the return value/status of the procedure.

Steven

 

[TJRTech]

Why do you want or need to surpress it?

You don't have to do anything with it if you don't want to and the sending back the return value is an integral message in the Tabular Data Stream (TDS) protocol that SQL Server uses.

TR

 

[stevensteven]

It returns a row, creating a unwanted resultset, making it VERY slow compared to other calls (updates/inserts) I need to do. If I could avoid returning it, it would speed up my calls by 10 times.

So the answer is no?

Thanks for the help,

Steven

 

[SQLSister]

I would find it hard to believe that a return value is slowing you up that much. I would bet some other part of your code is the problem. You didn't by chance use a cursor did you? Could you post the code?

 

[TJRTech]

I too would like to see the stored proc.

True SP return values are NOT returned as rows. They are an intrinsic return value that you cannot surpress.

TR

 

[bperry]

>It returns a row, creating a unwanted resultset,

This does sound a lot like you are talking about the message line that gets returned after a t-sql staement executes, indicating the number of rows affected.

To eliminate this message from being returned by your stored procedures, put this statement at the start of your SP:
SET NOCOUNT ON

from BOL: "SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft® SQL Server™ to execute queries, the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE."
------------------------
I don't think you can prevent what is known as the 'return value' (this is different from above) from being returned by your SP. On the other hand, I doubt very much that it is at the root of any performance problem.

 

[TJRTech]

Yes, StevenSteven...

Did you figure out whether or not the SP is returning a row (or more than one row) as would be the case IF it has a SELECT statement in it (check near the bottom).

Or, is it returning the number of rows affected "message", which can be turned off at the top of the SP with: SET NOCOUNT ON

The former will speed things up a little, but if it truely is returning a row, or more, than someone probably wrote the stored procedure that way to have that behavior.

Let us know and we can help.

TR