Store result of dynamic SQL generating XML to XML Variable

Question

So I have found plenty of examples across SO that show how to assign the result of dynamic SQL to variables but I have yet to find one that does it the way that I am trying to... So either I am going about this the wrong way or I just haven't found the proper syntax to perform this correctly.

Basically, I am trying to create an XML record of a row from a table before deleting the row from the table (think of it as an audit of the table) using a Stored Procedure. I was attempting to do this through dynamic SQL so that the Stored Procedure can be called from multiple other Stored Procedures that perform Delete operations on their corresponding tables, but before doing so they pass the necessary information (the @sql variable which will contain the select statement to obtain the record) to this Audit Stored Procedure.

Create Procedure dbo.AuditDelete(
@sql NVARCHAR(200),
@TableName VARCHAR(50),
@UserName NVARCHAR(256)
)
AS
  DECLARE @XML XML
BEGIN
  --at this point @sql will contain a select statement written by the calling stored procedure
  --which is specifically written to return the record that is being deleted, example:
@sql = select * from my.table where tableId = 1 FOR XML AUTO

  execute sp_executesql @sql, N'@XML XML OUTPUT', @XML = XML OUTPUT;
  SELECT @XML 
              --I was doing this as a check to ensure that XML has the record
              --because the execute command returned the XML record, but @XML is null...
              --and I can't figure out why

  --code for inserting into the Audit table

So I'm sure that its either some syntax I'm missing or perhaps I'm just going about this the wrong way. Any suggestions?

Note: this is being done in SSMS on SQL Server 2008 R2

Mikael Eriksson · Accepted Answer · 2014-04-03 15:23:16Z

7

You need to assign the result your query to the parameter in the dynamic SQL.

set @sql = N'set @XML = (select * from my.table where tableId = 1 FOR XML AUTO)'
execute sp_executesql @sql, N'@XML XML OUTPUT', @XML OUTPUT

answered Apr 3, 2014 at 15:23

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jfabs Over a year ago

Oh. I see that now on the other question I was examining... Man do I feel foolish haha. Would you expect that if I made this change and ran a test (declare @sql and @xml, and then what you have written, then select @XML) that I should only see 1 result with that result being the XML record?

Collectives™ on Stack Overflow

Store result of dynamic SQL generating XML to XML Variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related