SPONSORS:






User Tag List

Page 1 of 2 12 LastLast
Results 1 to 10 of 12
  1. #1
    Junior Member
    Join Date
    Dec 1999
    Location
    Allendale, NJ, US
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Returned OUTPUT from a Stored Procedure

    I'm having problems getting the output from a SQL db stored procedure. Although I pass it an INT variable, it returns an error saying it cannot convert a nvarchar to an INT. Is the variable name being passed as a literal?

    The syntax I'm using is:
    INT USERID
    DB_ExecuteSql (hdbc, "EXECUTE AddUser USERID, 'user' 'password'")

    AddUser is the stored procedure and user and password are input parameters.

    I have no problems executing stored procedures that don't have OUTPUT parameters.

    Any suggestions?

    ------------------

  2. #2
    Super Member
    Join Date
    Jul 2001
    Location
    Earth
    Posts
    1,882
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Returned OUTPUT from a Stored Procedure

    Try passing an ANYTYPE variable and see what happens

    ------------------
    James Soderborg
    http://www.ameliortech.com
    jamesso@ameliortech.com

  3. #3
    Senior Member
    Join Date
    Aug 1999
    Location
    Cambridge, UK
    Posts
    470
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Returned OUTPUT from a Stored Procedure

    nvarchar sounds like a string-like variable type.

    ------------------

  4. #4
    Junior Member
    Join Date
    Dec 1999
    Location
    Allendale, NJ, US
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Returned OUTPUT from a Stored Procedure

    Thanks for the response guys.

    I did try ANYTYPE ans STRING but I'm pretty sure the error was passed back from the SQL server or ODBC. Doesn't seem to know or care what data type I declare it as.
    I can always run it as a batch file using SYS_EXECUTE, but I would prefer being able to use in line code. Easier to maintain.

    I'm not giving up yet.



    ------------------

  5. #5
    Senior Member
    Join Date
    Jun 2000
    Location
    Hartford, CT, USA
    Posts
    163
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Returned OUTPUT from a Stored Procedure

    You were right. nvarchar is a 'Variable Character' data type...
    Try giving your strings without the single quote.
    Would syntax for Regular Expression required here?

    ------------------
    Jaimini Bhatt
    jaiminita@hotmail.com
    jaiminita@yahoo.com
    Jaimini Bhatt

  6. #6
    Senior Member
    Join Date
    Aug 1999
    Location
    Cambridge, UK
    Posts
    470
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Returned OUTPUT from a Stored Procedure

    If it's a problem upstream of silktest, do you also get it when executing the same stored procedure from a different odbc client? If so, then you're a bit stuck. If not, then it's worth reporting to segue as a straight-out bug.

    ------------------

  7. #7
    Junior Member
    Join Date
    Dec 1999
    Location
    Allendale, NJ, US
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Returned OUTPUT from a Stored Procedure

    The problem is on the SQL server end. I created a simple stored procedure that has one input and one output paramater, both int.

    The correct syntax for getting back the output from a stored procedure is:

    hSQL = DB_ExecuteSQL( hDB, "EXECUTE sp_OutputTest arg_in, @arg_out = @my_arg_out OUTPUT")

    @arg_out is the int variable declared in the stored procedure. @my_arg_out is where the output returned from the SP will be stored.

    This results in SQL server error "Must declare the variable @my_arg_out".

    I can successfully execute the SP using SQL Query Analyzer using this syntax:

    DECLARE @my_arg_out int
    EXECUTE sp_OutputTest arg_in, @arg_out = @my_arg_out OUTPUT
    SET my_arg_out = @my_arg_out

    So it appears the return output variable needs to be declared on the server side and then its value is available when the SP returns.

    Looks like I need to be able to execute this in one SQL string.
    Is this doable?



    ------------------

  8. #8
    Senior Member
    Join Date
    Oct 2001
    Location
    NC, United States
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Returned OUTPUT from a Stored Procedure

    just looking for further clarification......

    the output from a DB_Exec....is stored onto the hSQL variable (think so). Not sure you could assign a variable in ST with a SQL output val directly in you SQL statement...

    ------------------
    -gram
    -gram

  9. #9
    Junior Member
    Join Date
    Dec 1999
    Location
    Allendale, NJ, US
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Returned OUTPUT from a Stored Procedure

    gram,

    Your right.
    The statement 'SET my_arg_out = @my_arg_out' won't work. I got ahead of myself.
    Oh well, I guess I'll use the batch file method.

    Thanks all for your thoughts.
    Billy


    ------------------

  10. #10
    Senior Member
    Join Date
    Oct 2001
    Location
    NC, United States
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Returned OUTPUT from a Stored Procedure

    a suggestion...
    you could send a second query to retrieve the output value....probably return as a string and then typecast to int.

    ------------------
    -gram
    -gram

 

 
Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.0.9 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Questions / Answers Form provided by vBAnswers (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominatevBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 07:19 AM.

Copyright BetaSoft Inc.