May 2, 2012 at 1:08 pm
Hi All-
I've been trying to solve this one for days with no avail. I'm using sp_executesql to run some dynamic sql that I build based on a variable number of columns in a table that will change with each table that I iterate through. No problem building the variables columns and such, but I'm trying to return an XML stream into a variable through the sp_executesql and am having some problems getting the return value correct. Here's an example of what I'm trying to do. This is just a small code snippet from a much larger routine so I'm not including everything for brevity's sake but can supply additional info if needed:
SET @FetchExceptsql = N'FETCH NEXT FROM DELTA_EXCEPTS INTO ' + @DeltaVar
SET @OldValueSelect = 'SELECT * FROM COMPARETABLE WHERE ' + @DeltaVarColumn + ' FOR XML PATH(''' + @TableName + '''), ELEMENTS XSINIL, TYPE'
SET @ExecuteSQL = @DeltaDeclare + ';' + @FetchExceptsql + ';' + @OldValueSelect
EXEC sp_executesql @ExecuteSQL, N'@OldValues nvarchar(4000) OUTPUT', @OldValues OUTPUT
Since the select statement returns an XML stream I want to assign that to a variable that I return from the sp_executesql. The select works correctly but I can't seem to be able to assign it to a variable. I can't use something like "SELECT @OldValues = * from ..." because it errors of course. Everything works great except how to get the XML assigned to a variable. I'm tempted to build another table that I can stuff the XML into a column in my sp_executesql command but I'd rather not if there's another way to do it.
Thanks
May 2, 2012 at 1:47 pm
Define another output, and make the Select populate it inside the dynamic SQL.
SET @OldValueSelect = 'select @myxmloutput = (SELECT * FROM COMPARETABLE WHERE ' + @DeltaVarColumn + ' FOR XML PATH(''' + @TableName + '''), ELEMENTS XSINIL, TYPE)'
Your existing Select gets wrapped in parentheses, and the rest is the part I typed in lowercase. Make it the style you want, I just used lowercase to make it visible in this sample.
Does that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2012 at 2:43 pm
*Smacks self in head*
I was thinking something in that direction but couldn't actually extract it. Worked like a charm.
Thanks!
May 3, 2012 at 7:14 am
You're welcome.
Sometimes it's the most obvious things that are the best hidden.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply