how to assign results from exec to variable

Question

How do I assign the results of an exec command to a variable. like the below, so when I do select @sql2 I get the result of the executed varchar sql.

declare @sql varchar(500)
declare @sql2 varchar(max)
set @sql = 'SELECT 
                PDB.OutletBrandID, OB.BrandName
            FROM 
                ProductDistributionBrand PDB
            INNER JOIN 
                [IND_roadchef].dbo.OutletBrands OB 
            ON 
                PDB.OutletBrandID = OB.OutletBrandID

            FOR XML PATH(''ProductDistributionBrandDetail''),ROOT(''ProductDistributionBrandDetails''),TYPE'    
--select @sql
set @sql2 = exec(@sql)  
select @sql2

Seems similar to http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable — P.Salmon, Jun 07 '16 at 10:31

score 2 · Accepted Answer · answered Jun 07 '16 at 10:30

2

Use an output param:

declare @sql nvarchar(500)
declare @xml XML

set @sql = 'set @xml = (SELECT ..... FOR XML PATH(''ProductDistributionBrandDetail''),ROOT(''ProductDistributionBrandDetails''),TYPE)'    

EXEC sp_executesql @sql, N'@xml XML output', @xml = @xml OUTPUT

select @xml

answered Jun 07 '16 at 10:30

Alex K.

171,639
30
264
288

the select @xml returns a result of null – user3266033 Jun 07 '16 at 10:44

score 0 · Answer 2 · answered Jun 07 '16 at 10:24

You can use Table Variables. Try like this,

DECLARE @sql TABLE (col1 VARCHAR(500))
DECLARE @sql2 VARCHAR(max)

SET @sql2 = 'SELECT 
                PDB.OutletBrandID, OB.BrandName
            FROM 
                ProductDistributionBrand PDB
            INNER JOIN 
                [IND_roadchef].dbo.OutletBrands OB 
            ON 
                PDB.OutletBrandID = OB.OutletBrandID

            FOR XML PATH(''ProductDistributionBrandDetail''),ROOT(''ProductDistributionBrandDetails''),TYPE'

INSERT INTO @sql
EXEC (@sql2)

SELECT *
FROM @sql

Please post your sample output – StackUser Jun 07 '16 at 10:29 — StackUser, Jun 07 '16 at 10:29

score 0 · Answer 3 · answered Jun 07 '16 at 10:30

0

Please see reference from this link it will show you how to use output parameters

https://support.microsoft.com/en-us/kb/262499

answered Jun 07 '16 at 10:30

Myo Myint Aung

147
1
3

Your answer in itself does not provide and answer to the question, links should supplement an answer, not be the entirety of it. So although the link you have posted is useful and more or less answers the question, your answer should at the very least summarise the article, and include bits relevant to this particular question, so that in the event that the website moves or goes down, your answer still remains useful to future visitors. – GarethD Jun 07 '16 at 10:47

how to assign results from exec to variable

3 Answers3