OutPut Parameter

  • DuruG

    SSCrazy

    Points: 2029

    Hi everybody!

    I have this SP that is returning a recordset a in a output variable the rowcount. But somehow the output variable is empty. Can somebody give me a hint?

    Thanks,Durug

    CREATE PROCEDURE [sp_inventory_Class_click]

    (

    @clickclass char(2),

    @countNr int OUTPUT

    )

    AS

    BEGIN

    SELECT item_class,

    sum(quantity_on_hand * frozen_unit_cost)

    AS Item_Valuation

    FROM Item_Master AS a INNER JOIN

    Item_frozen_cost AS b ON

    a.location_code = b.location_code AND

    a.item_number = b.item_number

    where Inactive_code <> 'I'

    and substring(item_class,1,2) = @clickclass

    group by item_class

    ORDER BY Item_valuation desc

    select @countNr = @@rowcount

    END

  • Antares686

    SSC Guru

    Points: 125444

    How are you retrieving your value, and can you post the relavent code?

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • DuruG

    SSCrazy

    Points: 2029

    set cmd = Server.CreateObject("ADODB.Command")

    cmd.ActiveConnection = "Provider=SQLOLEDB;Server=local;Database=INTRA;UID=sa;PWD="

    cmd.CommandText = "sp_inventory_Class_click"

    cmd.CommandType = 4

    cmd.Parameters.Append cmd.CreateParameter("@clickclass",adChar,adParamInput,2, Label)

    cmd.Parameters.Append cmd.CreateParameter("@countnr",adInteger,adParamOutput)

    set objrs = cmd.Execute

    response.write cmd.parameters("@countnr")

  • Andy Warren

    SSC Guru

    Points: 119694

    Have to close the recordset to get the parameter.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Antares686

    SSC Guru

    Points: 125444

    Try response.write cmd.parameters(1) (may be 0 but try 1 first).

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • DuruG

    SSCrazy

    Points: 2029

    Apparently is has to do something with the cursor location.

    I changed the code to this

    set cn = Server.CreateObject("ADODB.Connection")

    set cmd = Server.CreateObject("ADODB.Command")

    set objrs = Server.CreateObject("ADODB.Recordset")

    cn.CursorLocation = adUseClient ' Uncomment this line to get the output parameter early.

    cn.Open "Provider=SQLOLEDB;Server=local;Database=INTRA;UID=sa;PWD="

    set cmd.ActiveConnection = cn

    cmd.CommandText = "sp_inventory_Class_click"

    cmd.CommandType = 4

    cmd.Parameters.Append cmd.CreateParameter("@clickclass",adChar,adParamInput,2, Label)

    cmd.Parameters.Append cmd.CreateParameter("@countnr",adInteger,adParamOutput)

    set objrs = cmd.Execute

    And it's working. As you can see from the code if you comment this line cn.CursorLocation = adUseClient (then the location will be server side) the parameter is empty again.

    Anyway,

    thanks guys!

  • Antares686

    SSC Guru

    Points: 125444

    Glad to hear you found a solution.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply