How to get out EXEC(T-SQL) data?

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 4, 2004 at 8:52 am

  #497326

  If the points is all that matters I may as well earn them RIGHT 

  
  * Noel

• Manisha-154097

  Newbie

  Points: 5

  More actions

  July 3, 2004 at 4:51 am

  #513237

  i'm using sp_executesql to get the output. but doesnt work.

  however, the select statement gives the output.

  refer:

  SET QUOTED_IDENTIFIER OFF

  set @stmnt = "SELECT @description = Dscrptn + coalesce(' for ' + CaseNumber, '') + coalesce(' ' + CaseName, '') from OPENQUERY([SA], 'select " + @prefix + "sComments as Dscrptn, cassCaseName as CaseName, cassCaseNumber as CaseNumber from PW_SMA_SQL_AlphaTestCopy1.dbo." +

     @table + " left outer join PW_SMA_SQL_AlphaTestCopy1.dbo.sma_TRN_Cases on " +

     "casnCaseID = " + @prefix + "nCaseID where " +

     @colname + " = 424')"

  print @stmnt

  exec sp_executesql @stmnt, N'@description varchar(4000) OUTPUT', @description

  print ' 1 ' + @description

  SELECT @description = Dscrptn + coalesce(' for ' + CaseNumber, '') + coalesce(' ' + CaseName, '')

  from OPENQUERY([SA], 'select aptsComments as Dscrptn, cassCaseName as CaseName, cassCaseNumber as CaseNumber

     from PW_SMA_SQL_AlphaTestCopy1.dbo.sma_TRN_Appointments

     left outer join PW_SMA_SQL_AlphaTestCopy1.dbo.sma_TRN_Cases on 

     casnCaseID = aptnCaseID where aptnAppointmentID = 424')

  print ' 2 ' + @description

  set @stmnt = "SELECT Dscrptn + coalesce(' for ' + CaseNumber, '') + coalesce(' ' + CaseName, '') from OPENQUERY([SA], 'select " + @prefix + "sComments as Dscrptn, cassCaseName as CaseName, cassCaseNumber as CaseNumber from PW_SMA_SQL_AlphaTestCopy1.dbo." +

     @table + " left outer join PW_SMA_SQL_AlphaTestCopy1.dbo.sma_TRN_Cases on " +

     "casnCaseID = " + @prefix + "nCaseID where " +

     @colname + " = 424')"

  exec sp_executesql @stmnt

  SET QUOTED_IDENTIFIER ON

  the select statement after the first print is the contents of the variable given to sp_executesql. the second print statement gives the output as desired.

  also, the statement after the second print statement prints the desired output. but i want that output in a variable. the entire purpose being i want to use this in an update statement.

  thanks.