SQL Server 2000 Output Parameters

Question

I have created a stored procedure in SQL Server 2000 and within the stored procedure I have created a number of variables that I want to return to the asp classic script.

I have tried declaring the variable like this:

DECLARE @output int OUTPUT

But SQL Server then says that I cannot declare an output variable that way.

So instead, I declared the variable at the beginning of the stored procedure along with the parameters that I am passing from my ASP script. This seems to work but my ASP script now reports an error saying that the stored procedure is expecting certain parameters.

Should I add these parameters into my command object and pass them as NULLs or is there another way to do this.

Thanks

Answer 1

There's a couple of things you need to do:

1) make sure you've defined the OUTPUT parameter in the stored procedure correctly e.g.

CREATE PROCEDURE dbo.ExampleSproc
    @output INTEGER OUTPUT
AS
...

2) when calling the sproc from ASP, you need to add the @output parameter to the command, as an output parameter (adParamOutput)