New set parameters proc_output_params and set proc_return_status

The following new parameters should be included in the set command reference section of Reference Manual: Commands.

Parameters

• proc_output_params – controls sending of output parameters that a stored procedure generates back to the client. set proc_output_params off suppresses sending the output parameters back to the client. The default for this parameter is on.

• proc_return_status – controls sending of a return status TDS token back to the client. set proc_return_status off suppresses sending the return status token to the client, and isql client does not display the (return status = 0) message. The default for this parameter is on.

  WARNING! If the client application that executes a procedure relies on the success or failure of the procedure based on the return status, then do not use the set proc_return_status off option.

Example 1

Suppresses the output of both parameters and the return status TDS token:

1> set proc_output_params OFF
2> go

1> set proc_return_status OFF
2> go

1> declare @x int
2> select @x = 2
3> exec sp_pout @x output
4> print "Value of @x returned from sproc is: %1!", @x
5> go(1 row affected)
Value of @x returned from sproc is: 3
(1 row affected)

In addition, you can also suppress the lines reporting the number of rows affected to generate output with no extra messages using the set nocount on option before running this batch.

Example 2

Suppresses the output of parameter information:

1> create procedure sp_pout (@x int output) as select @x = @x + 1
2> go

1> set proc_output_params off
2> go

1> declare @x int
2> select @x = 1
3> exec sp_pout @x output
4> print "Value of @x returned from sproc is: %1!", @x
5> go

(1 row affected)
(return status = 0)
Value of @x returned from sproc is: 2

If you do not perform set proc_output_params off, the output after (return status = 0) includes the following:

Return parameters:

 ----------- 
            2

Copyright © 2004. Sybase Inc. All rights reserved.

View this release bulletin as PDF