Post reply

Passing value from stored procedure to DOS script

  • August 30, 2005 at 9:06 am

    #89594

    Hi All,

    Does anybody know how to pass a value from Stored Procedure to DOS script variable?

    In my script I execute SQL stored procedure which returns a numeric value back to the script. I want to assign this value to a variable.

    I figured out how to return the value to a file, and later parse it:

        OSQL -Usa –SMy_WKS -Psapwd –Qtest_db..usp_jobs_status -o%JOBS%

        for /f "tokens=1" %%a in ('type jobs.txt') do ( set FAILEDJOB_CNT=%%a )

     

    but, I think there should be a simpler way to do it, without writing to a file.

    Does anybody know the correct syntax for DOS and St.Proc?   

     

    Appreciate your help.         

     

  • August 30, 2005 at 9:19 am

    #586180

    Looks like your already on the right track.

    Don't think theres's a way.

    http://www.sqlteam.com/item.asp?ItemID=744

    Perhaps write an app in an easy to use language like vb.

     

  • August 31, 2005 at 3:40 am

    #586390

    I'm assuming you want to run a .cmd or .bat script (or something similar).

    If so then you can use xp_cmdshell, something like this:

    declare @cmd varchar(100)

    set @cmd='c:\bin\test.cmd ' + select something from somwhere where x=y

    exec master.dbo.xpcmdshell @cmd

    HTH

    Nigel
    How to post questions

  • September 1, 2005 at 12:15 am

    #586724

    The above one works..

    Replace the last line

    exec master.dbo.xpcmdshell @cmd

    with

    exec master.dbo.xp_cmdshell @cmd

    Cheers

    Kindest Regards,

    Mani

  • September 1, 2005 at 2:01 am

    #586743

    Oops! Sorry about the typo

    Nigel
    How to post questions

  • September 1, 2005 at 4:03 am

    #586764

    You can try using EXIT in the SQL file

    EXIT(set nocount on

    DECLARE @ReturnCode INT

    Exec YourStoreProc @retstatus=@ReturnCode output

    select ISNULL(@ReturnCode,7))

    In the cmd / bat file after the osql

    use %ERRORLEVEL% variable to get the value of the Returncode in the bat file

    I have this methodology implemented sucessfully, so this should solve your problem.

    Cheer

  • September 1, 2005 at 9:53 am

    #586892

    Thank you Nitin,

     

    It almost worked. I can see the returning value “1” in the DOS window when the script is executed, but still can not capture this value into the batch script variable. The retcd var displays -100 instead of expected 1 sent by SQL statement.

     

    Here is a piece of .bat script:

    OSQL -Usa –SMy_WKS -Psapwd -Q "EXIT(Exec test_db..usp_jobs_status 0)"

    set retcd=%ERRORLEVEL%

    echo %retcd%

     

    The stored procedure looks like:

     

    CREATE procedure usp_jobs_status

    @OutVal smallint OUT

    as

     

    select @OutVal = count(*) from.....

     

    return @OutVal

     

    Any idea what I am missing here?

    Thanks a lot for your help.

  • September 1, 2005 at 11:53 am

    #586970

    use select query in the exit block, then the value would be avalaible for

    %errolevel% variable

    osql -E -SMSHSRMNSUKD0311 -Q "EXIT(Declare @asd smallint Exec test_db..usp_jobs_status @OutVal=@asd OUTPUT select @asd)

    echo %ERRORLEVEL%

    Or insted modify your procedure to select query rather than using output variable

     

  • September 1, 2005 at 4:40 pm

    #587059

    Thank you Nitin!

    It worked!

Viewing 9 posts - 1 through 8 (of 8 total)

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