OUTPUT issue with sp_executesql

• lassell

  Ten Centuries

  Points: 1215

  More actions

  May 13, 2012 at 4:43 pm

  #104148

  Hi,

  I wonder if anyone can answer this/help. I am trying to select a blob of XML and then put this XML into a variable like so:

  DECLARE @retval XML

  DECLARE @sSQL nvarchar(500);

  DECLARE @ParmDefinition nvarchar(500);

  DECLARE @tablename nvarchar(50)

  DECLARE @ApplNbr VARCHAR(20)

  SET @ApplNbr = 24

  SELECT @tablename = N'[myDB].[dbo].[myTable]'

  SELECT @sSQL = N'SELECT * FROM ' + @tablename + 'WHERE [ApplNbr] = '+ @ApplNbr + '

  FOR XML PATH(''myDB.myTable'')';

  SET @ParmDefinition = N'@retvalOUT XML OUTPUT';

  EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;

  SELECT @retval; --<------- Why does this return NULL?

  However, I want the XML to to set to the @retval var, but this seems to lose its value once the SP has run and OUTPUT. How can I get this var to retain this value as I need to concatenate this into one xml blob?

  SET @OrigXML = (SELECT @retval

  ,@retval2

  ,@retval3

  FOR XML PATH (''), ROOT('Table'))

  Cheers, Phil

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  May 13, 2012 at 6:00 pm

  #1486903

  You need to assign to @retvalOUT inside the sp_ExecuteSQL string,

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• lassell

  Ten Centuries

  Points: 1215

  More actions

  May 13, 2012 at 6:12 pm

  #1486906

  Thanks, but I still get the same results. Is the syntax correct:

  DECLARE @retval XML

  DECLARE @sSQL nvarchar(500);

  DECLARE @tablename nvarchar(50)

  DECLARE @ApplNbr VARCHAR(20)

  SET @ApplNbr = 24

  SELECT @tablename = N'[amsbranch].[dbo].[tAppl]'

  SELECT @sSQL = N'DECLARE @retvalOUT XML

  SELECT * FROM ' + @tablename + 'WHERE [ApplNbr] = '+ @ApplNbr + ' FOR XML PATH(''amsbranch.tAppl'')' ;

  EXEC sp_executesql @sSQL

  SELECT @retval

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  May 13, 2012 at 6:51 pm

  #1486909

  phil.layzell (5/13/2012)

  ---

  Thanks, but I still get the same results. Is the syntax correct:

  ...

  No. Like this:

  DECLARE @retval XML

  DECLARE @sSQL nvarchar(500);

  DECLARE @ParmDefinition nvarchar(500);

  DECLARE @tablename nvarchar(50)

  DECLARE @ApplNbr VARCHAR(20)

  SET @ApplNbr = 24

  SELECT @tablename = N'[myDB].[dbo].[myTable]'

  SELECT @sSQL = N'

  SELECT @retvalOUT = (SELECT * FROM ' + @tablename + 'WHERE [ApplNbr] = '+ @ApplNbr + '

  FOR XML PATH(''myDB.myTable'') )';

  SET @ParmDefinition = N'@retvalOUT XML OUTPUT';

  EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;

  SELECT @retval; --<------- Why does this return NULL?

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• lassell

  Ten Centuries

  Points: 1215

  More actions

  May 13, 2012 at 6:57 pm

  #1486910

  Doh! Thanks that was too simple 🙂

  Cheers, Phil

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  May 14, 2012 at 7:08 pm

  #1487487

  Glad I could help!

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]