one value from a dynamic sql

• ishaan99

  SSCertifiable

  Points: 6261

  More actions

  February 11, 2004 at 10:13 am

  #160744

  USE msdb

  DECLARE @TXT AS NVARCHAR(50)

  SET @TXT = N'sp_help_job @job_name = ''SWLTEST'', @job_aspect = ''JOB'''

  Select current_execution_status FROM (sp_executesql N'sp_help_job @job_name = ''XXXXX'', @job_aspect = ''JOB''')

   

  Is there a way to get just one value i.e current_execution_status from teh above sql? any help will be greatly appreciated

  TIA

• Russel Loski

  SSCertifiable

  Points: 7054

  More actions

  February 11, 2004 at 6:40 pm

  #493730

  http://support.microsoft.com/default.aspx?scid=KB;EN-US;q262499

  At the bottom is one of their code samples, that I think is most helpful.

  Russel Loski, MCSD

  DECLARE @SQLString NVARCHAR(500)

  DECLARE @ParmDefinition NVARCHAR(500)

  DECLARE @IntVariable INT

  DECLARE @Lastlname varchar(30)

  SET @SQLString = N'SELECT @LastlnameOUT = max(lname)

                     FROM pubs.dbo.employee WHERE job_lvl = @level'

  SET @ParmDefinition = N'@level tinyint,

                          @LastlnameOUT varchar(30) OUTPUT'

  SET @IntVariable = 35

  EXECUTE sp_executesql

  @SQLString,

  @ParmDefinition,

  @level = @IntVariable,

  @LastlnameOUT=@Lastlname OUTPUT

  SELECT @Lastlname

   

  Russel Loski, MCSE Business Intelligence, Data Platform