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

Question

I have a scenario where essentially I need to cancel an order from an asp page, calling a SQL stored procedure, and then the stored procedure needs to send back generic responses to be displayed or used on the ASP page...but I don't know what statements I need on the asp side and sql side to make it 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 the stored procedure will look something 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)
AS
BEGIN
    SET @status = 'A' --TEST VALUE

    -- Insert statements for procedure here
    IF @order_id <> '' AND @order_id IS NOT NULL
    BEGIN
        IF @status = '' OR @status = 'A' OR @status = 'B'
        BEGIN
            --Insert cancellation record into cancel table
            EXEC sp_insert_cancel @order_id
                   --NEED TO SEND BACK STATIC (SUCCESS) MESSAGE TO ASP PAGE HERE
        END
    END
    ELSE
    --NEED TO SEND BACK STATIC (ERROR) MESSAGE TO ASP PAGE HERE
END

have you considered using a boolean/bit output parameter, that simply returns true if cancelled and false otherwise? — Tanner
– Tanner, Commented Jun 27, 2014 at 14:45
Is your plan to use an OUTPUT parameter in your stored procedure? Because you can use adParamOutput and return a numeric "error code" value. — Bond
– Bond, Commented Jun 27, 2014 at 14:50
Hi @Bond, it sounds like I'm looking for something like you're suggesting. How would the code look on each end? — kyle_13
– kyle_13, Commented Jun 27, 2014 at 15:01

Bond · Accepted Answer · 2014-06-27 15:34:58Z

2

Here's one way, using an output parameter in your stored procedure.

Modify your stored procedure to include an output param:

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

And assign it the proper error/return message:

SET @message = 'Error: I got confused.'

In your VBScript code, add the param to your command object:

Set prmFields = cmdAdmin.CreateParameter("Message", adVarChar, adParamOutput, 40)
cmdAdmin.Parameters.Append prmFields

Later, after you execute your command, you can retrieve its output/return value:

cmdAdmin.Parameters("Message").Value

This is just an example of passing back a value (a string in this case) from a stored procedure. You could pass back a numeric error value instead, and lookup the error description as part of your ASP page, or pass back a simple boolean (true/false, pass/fail) value. Up to you.

edited Jun 27, 2014 at 15:34

answered Jun 27, 2014 at 15:01

Bond

16.3k6 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user692942 Over a year ago

It's worth noting that output parameters can only be accessed once all resultsets have been returned.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related