sql server - Send values from asp page to SQL stored procedure and return value? -

i have scenario need cancel order asp page, calling sql stored procedure, , stored procedure needs send generic responses displayed or used on asp page...but don't know statements need on asp side , sql side make work?

for example:

asp:

dim cmdsearch, prmfields set cmdcancel = server.createobject("adodb.command") set prmfields=cmdcancel.createparameter("@order_id",adnumeric,adparaminput,9, order_id)  prmfields.precision=9 cmdadmin.parameters.append prmfields set prmfields=cmdadmin.createparameter("@customer_id",adnumeric,adparaminput,5, customer_id) prmfields.precision=5 cmdadmin.parameters.append prmfields cmdcancel.commandtext = "sp_cancel_order" 

then stored procedure like:

alter procedure [dbo].[sp_cancel_order]     --order id passed     @order_id numeric(9,0),     --customer id passed     @customer_id numeric(5,0),     @status char(1) begin     set @status = 'a' --test value      -- insert statements procedure here     if @order_id <> '' , @order_id not null     begin         if @status = '' or @status = 'a' or @status = 'b'         begin             --insert cancellation record cancel table             exec sp_insert_cancel @order_id                    --need send static (success) message asp page here         end     end     else     --need send static (error) message asp page here end 

here's 1 way, using output parameter in stored procedure.

modify stored procedure include output param:

@order_id numeric(9,0), @customer_id numeric(5,0), @status char(1), @message nvarchar(40) output 

and assign proper error/return message:

set @message = 'error: got confused.' 

in vbscript code, add param command object:

set prmfields = cmdadmin.createparameter("message", advarchar, adparamoutput, 40) cmdadmin.parameters.append prmfields 

later, after execute command, can retrieve output/return value:

cmdadmin.parameters("message").value 

this example of passing value (a string in case) stored procedure. pass numeric error value instead, , lookup error description part of asp page, or pass simple boolean (true/false, pass/fail) value. you.