Re: Return value from Stored Procedure

After playing with the code for awhile, especially with this

Set rsID = rsID.NextRecordset

I found out that on the Sql Server 2000, I need this line, and yet on the Express, I can do without it, what gives?

TIA


Bob Barrows [MVP] wrote:
1. When you execute a query in Query Analyzer, did you ever notice those "x rows affected" messages? Well, it's not just QA that those messages get delivered to. They get returned to ADO as recordsets ...
2. SQL Server does not return the output or return parameter values until all recordsets generated by the procedure are consumed

Setting NOCOUNT to off prevents those extra resultsets from being generated and returned.

Light wrote:

Hi Bob,

That does it, but why?

Thanks a billion, you just save my life:).

Bob Barrows [MVP] wrote:

Light wrote:


CREATE Procedure NewCompany
(
@varCompanyName varchar (50) = null,


@RETURN_VALUE int OUTPUT
)
As


You need to add "SET NOCOUNT ON" to this procedure - right here as a
matter of fact.


.

Relevant Pages

  • Problem with the Legacy ASP files and the Sql Server Express
    ... I inherited some legacy ASP codes in my office. ... One of the pages is using a stored procedure to create a new table and ... Set rsID = rsID.NextRecordset ... On the SQL Server 2000, the ID is returned correctly, but the Express ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: returning success of exec(@sql) into a variable
    ... Should have used the 'national' designator for the @parameters string ... exec sp_executesql @strsql, N'@Result int OUTPUT', @Result OUTPUT ... That tells SQL Server that the string is Unicode. ... I'm running this and butt i'm getting the error message ...
    (microsoft.public.sqlserver.server)
  • Re: How to get a SP return value on another SP?
    ... You could use an OUTPUT parameter for this. ... CREATE PROCEDURE inner_sp @x int OUTPUT AS ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Best practice - handling null in sp
    ... the calling asp script that pass values to an sql server Sp. ... oCmd.Parameters.append oCmd.CreateParameter("u_name", adVarChar, ... @u_id int output ...
    (microsoft.public.inetserver.asp.general)