SQL Server RETURN how to with asp

Forum Moderators: open

Message Too Old, No Replies

SQL Server RETURN how to with asp

topr8

2:44 pm on May 17, 2007 (gmt 0)

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

2:15 am on May 18, 2007 (gmt 0)

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

shorepound

2:27 pm on May 22, 2007 (gmt 0)

why not just access the records with your recordset object?

in your asp page:

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

topr8

3:28 pm on May 22, 2007 (gmt 0)

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

5:48 am on May 23, 2007 (gmt 0)

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.