return code from stored procedure in Sql management studio query window

• sqlfriend

  SSC Guru

  Points: 52794

  More actions

  September 23, 2011 at 2:11 pm

  #390466

  I have a stored procedure we use for returning suceed or failure code to a commandline batch file.

  I would like to test it in management studio first,

  So I run the stored procedure, and I purposely made the sql agent job run failed, so that the run_job sproc can show me in the message panel in ssms a return code like 1, but i cannot get it.

  what could be wrong with the sproc?

  Here is the statement I excute the sproc,

  EXECUTE [Asset5].[dbo].[RUN_JOB]

  @JOB_TO_RUN='testjob'

  GO

  Then I got the message:

  Job 'testjob' started successfully.

  Msg 50000, Level 16, State 1, Procedure RUN_JOB, Line 103 @CURRENT_RUN_STATUS = 0

  I expected something like return code=1.

  THanks

• sturner

  SSC-Insane

  Points: 22411

  More actions

  September 23, 2011 at 2:55 pm

  #1386033

  execute @retCode = [Asset5].[dbo].[RUN_JOB] @JOB_TO_RUN='testjob'

  select @retCode

  The probability of survival is inversely proportional to the angle of arrival.

• sqlfriend

  SSC Guru

  Points: 52794

  More actions

  September 23, 2011 at 5:05 pm

  #1386062

  Thank you, that works, I got the return number 1.

  Now further step of my project, I would like to use a bat file to call this stored procedure and if the stored procedure failed - returns 1 to the batch file, and the batch file somehow can know the sproc failed.

  Is there a way to do this?

  I thought I may be able to use sqlcmd in the batch file, the hard part is how in the batch can know the sproc failed and also return 1 to the software that calls this batch?

  Thanks