SQL Server RETURN how to with asp

[topr8]

am running a stored procedure in the following way from an asp page:

set rsMyrecordset = conn.execute("spMyStordProc @myparameter")

it returns a recordset that is easy enough to use,

however if the stored procedure produces an error it should RETURN a number

i've written this in the procedure itself, something like:

SELECT foo, bar from tablename where id=@parameter

IF @@error<>0
RETURN 100

....

but how can i get the return value back in the asp page?

[bmcgee]

Use a Command object. Add a parameter with type returnvalue. After executing the proc, access that parameter.

[shorepound]

why not just access the records with your recordset object?

in your asp page:

You have <%= rsMyrecordset("foo") %>, and <%= rsMyrecordset("bar") %>

[topr8]

thanks, i was hoping i wouldn't have to use the command object.

>>why not just access the records with your recordset object?

>>in your asp page:

>>You have <%= rsMyrecordset("foo") %>, and <%= rsMyrecordset("bar") %>

yeah absolutely that would be how to get the values of the recordset, however if the stored procedure (which is actually a long series of sql instructions which ultimately returns the recordset) hits an error at any point, that error is returned and the rest of the procedure is not processed and it is not returned as a recordset.

[IndiaMaster]

I used a command object in the ASP page and an output variable in the stored procedure which stores the error. After executing the stored procedure, I tested the output variable and if it contains any error(in your case its a number), I print it.