2

I have an MSSQL stored procedure which explicitly sets an output parameter to a value. However, when I execute that stored procedure from an Classic ASP page using an ADODB command, the output parameter is null.

Stored procedure:

ALTER PROCEDURE [dbo].[recordResponse]
    @survey_id smallint OUTPUT, 
    @member_id varchar(10) OUTPUT,
    @response varchar(1000) OUTPUT,
    @comment varchar(1000) OUTPUT,
    @response_id int OUTPUT,
    @timestamp datetime OUTPUT,
    @status int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @timestamp = getdate();

    DECLARE @surveyExists as binary

    Select @surveyExists = 1 from surveys where survey_id = @survey_id;

    if (@surveyExists = 1)
        BEGIN
            insert into
                responses(member, [timestamp], response, comments, survey_id)
                values(@member_id, @timestamp, @response, @comment, @survey_id);

            set @response_id = SCOPE_IDENTITY();
        set @status = 200;
        END
    else
        set @status = 400;
END

Classic ASP:

    Set cmd = Server.CreateObject("ADODB.Command") 'Initiate the command object
    cmd.CommandType = 4 'Stored Procedure
    cmd.CommandText = "recordResponse" 'Name of the stored procedure

    cmd.ActiveConnection = connString 'Using which connection?

    'Add the parameters
    cmd.Parameters.Append cmd.CreateParameter("@survey_id", 2, 3, 0, 1)
    cmd.Parameters.Append cmd.CreateParameter("@member_id", 200, 3, 10, memberNo)
    cmd.Parameters.Append cmd.CreateParameter("@response", 200, 3, 1000, answer)
    cmd.Parameters.Append cmd.CreateParameter("@comment", 200, 3, 1000, comment)
    cmd.Parameters.Append cmd.CreateParameter("@response_id", 2, 2)
    cmd.Parameters.Append cmd.CreateParameter("@timestamp", 135, 2)
    cmd.Parameters.Append cmd.CreateParameter("@status", 3, 2)

    'Execute stored procedure
    Call cmd.Execute()

    Response.write "[" & cmd("@status") & "]"

This results in an output of [] whereas I am expecting an output of [200] or [400].

I have looked at various other similar threads and taken on board suggestions and solutions including iterating over the resulting recordset, but none have solved my problem.

Can anyone see where I am going wrong???

user692942
  • 16,398
  • 7
  • 76
  • 175
Matt P
  • 37
  • 5
  • Personally I use `cmd.Parameters.Refresh` to load the parameter definition from the proc. That should for example set the output attribute correctly (in your case you haven't done this) – Nick.Mc Mar 22 '18 at 03:33
  • 1
    @Nick.McDermaid: That causes a server round trip: I wouldn't recommend it's use. – Mitch Wheat Mar 22 '18 at 04:35
  • Also: "If you use the Refresh method to obtain parameter information from the provider and it returns one or more variable-length data type Parameter objects, ADO may allocate memory for the parameters based on their maximum potential size, which will cause an error during execution." : https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/refresh-method-ado – Mitch Wheat Mar 22 '18 at 04:36
  • You make good points. These points are balanced against the developer having to define the parameters seperately in both in the database and in the application (and know about parameter zero which is the return value, as well as the OUTPUT parameter trick). Anyway I believe you have addressed the root problem in your answer below. `Refresh` can certainly helpful even just for one-off metadata discovery - to know what the parameters should look like if you want to code them. – Nick.Mc Mar 22 '18 at 04:56
  • I wasn't aware of the `cmd.parameters.Refresh` method. Sounds very handy! Good caveats mentioned, too. – Matt P Mar 22 '18 at 05:41
  • Biggest caveat is Microsoft don’t recommend it for production environments. It’s also not supported by all providers so hit and miss method. I wouldn’t recommend it, besides configuring the parameters correctly isn’t that difficult. – user692942 Mar 22 '18 at 06:57
  • Are you trying to write ASP.Net because it’s Classic ASP VBScript code that you have posted in the question? – user692942 Mar 22 '18 at 07:04
  • OK, I'm not overly familiar with all the variants of the MS scripting world. I had it in my mind that ASP.Net = Classic ASP. Thanks for pointing it out. – Matt P Mar 23 '18 at 01:11

3 Answers3

1

The create parameter parameters are to be set like this. Set objparameter=objcommand.CreateParameter (name,type,direction,size,value) in ADO.

Please set the parameters, its type, direction & size correctly.

  Response.write "[" & cmd.Parameters("@status").Value & "]"
srp
  • 560
  • 1
  • 5
  • 14
  • Isn't the line `cmd.Parameters.Append cmd.CreateParameter("@status", 3, 2)` already doing this? – Matt P Mar 22 '18 at 05:38
  • the documentation says the first argument is name , then type and then direction & Value. In the above case first argument is correct, but the second argument should be the type. – srp Mar 22 '18 at 05:42
  • check this https://stackoverflow.com/questions/10017872/calling-sql-stored-procedure-with-output-parameter-in-vbscript – srp Mar 22 '18 at 05:43
  • Oh, I see what you mean now. I'm using the enum values rather than the labels. Value of 3 maps to label of adInteger, per [MS doco](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/datatypeenum). Similarly for the direction argument, value of 2 maps to a label of adParamOutput, per the [MS docs](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/parameterdirectionenum). – Matt P Mar 22 '18 at 05:49
  • you should also try putting the "return" at the end of procedure. – srp Mar 22 '18 at 06:02
  • @srp I assume by `RETURN` you are referring to the Stored Procedure because unless you are writing JScript in Classic ASP `return` isn't valid. Also, the parameter in question isn't an `adParamReturnValue` but an `adParamOutput` parameter `Type`, so setting `RETURN` in the Stored Procedure would do nothing. – user692942 Mar 22 '18 at 08:25
0

Please try 

cmd.Parameters["@status"].Value
Anton
  • 2,846
  • 1
  • 10
  • 15
0

I found the problem: I was using the wrong dataType for the parameter @response_id. I had adSmallInt (2), whereas it should have been adInteger (3) to match the data type declared in the stored procedure. MSSQL was throwing an exception, but my ASP script had On Error Resume Next specified which was silently swallowing the error. So the script ran and the SP executed the INSERT statement but couldn't get far enough to set the @status parameter, but the script kept running anyway. Once I commented out the On Error Resume Next, I could see the exception and found the culprit.

Thanks for all your suggestions anyway. Much appreciated!

Matt P
  • 37
  • 5
  • Very common and covered it multiple questions on [so] already. – user692942 Mar 22 '18 at 07:07
  • A useful resource I have used for checking data types is [Data Type Mapping](http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx), helped me out many times. – user692942 Mar 22 '18 at 08:28
  • Thanks. I know similar q's have been asked and I think I researched all of them! But none pointed me in the right direction for this problem. Most talked about retrieving all record sets before having access to output params, while others talked about having the right data type for the param you're interested in. None suggested to check if `On Error Resume Next` is being used as there could be an exception being swallowed that is causing the output parameter to never be set. – Matt P Mar 23 '18 at 01:16
  • 3
    @MattP that's because you used "the enum values rather than the labels". Such code is unreadable and often leads to errors. Also we assumed the SP was executed successfully as a developer can easiliy verify that by running SQL Profiler before asking for a help. Similar problem could be if SP is dropped, SQL Server is stopped, etc... such problems should be eliminated from the beginning. Anyway... we're glad you solved the issue. – Anton Mar 23 '18 at 02:52