Create String Template from two stored procedures

Question

I have two stored procedures that return result sets.

How can I use them to populate a string and update another column?

exec getEmailSignatureDetails 'Jane', 'Doe'

exec getFeaturedAccount 'June'

These both return columns that I would like to map to variables.

I would then like to put the variables into a string.

Then update a column in another table with that string.

Output from getEmailSignatureDetails:

addCity       | addLine     | addSt |  addZip | fName | lName
--------------+-------------+-------+---------+-------+------
San Francisco | 777 SV Lane | CA    |  94016  | Jane  | Doe

Output from getFeaturedAccount:

month | img      
------+----------
June  | base64...

I would like to turn this into a string like

Your package has been delivered to 
@fName @lName
@addLine
@addCity @addSt, @addZip
@img

And then update a column with this string matching on name.

How about showing us what the output of the procedures are? Also, what is the desired end result? I have a hunch I know what you want.. — Radu Gheorghiu, Jun 12 '18 at 15:07
Either insert their results in a temporary/variable table or use OUTPUT parameters. — EzLo, Jun 12 '18 at 15:07
Google `INSERT EXEC` then `OUTPUT` parameters. Choose one. Profit. — Richard Hansell, Jun 12 '18 at 15:10
See if this answers your question: https://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure — Tab Alleman, Jun 12 '18 at 15:30

John Cappelletti · Accepted Answer · 2018-06-12T16:29:59.420

If I understand your question your are looking to dynamically fill-in a template via macro substitution

Example

-- Create some Sample Data
Declare @getEmailSignatureDetails Table ([addCity] varchar(50),[addLine] varchar(50),[addSt] varchar(50),[addZip] varchar(50),[fName] varchar(50),[lName] varchar(50))
Insert Into @getEmailSignatureDetails Values 
 ('San Francisco','777 SV Lane','CA',94016,'Jane','Doe')

Declare @getFeaturedAccount Table ([month] varchar(50),[img] varchar(50))
Insert Into @getFeaturedAccount Values 
 ('June','base64..')



-- Declare the Template
Declare @Template varchar(max) ='
Your package has been delivered to 
@fName @lName
@addLine
@addCity @addSt, @addZip
@img
'

-- Populate the Template
Select @Template = replace(@Template,'@'+Field,Value)
 From ( 
        Select C.*
         From (values (convert(XML,(Select * From  @getEmailSignatureDetails Join  @getFeaturedAccount on [month]='June' For XML Raw ) ) ) ) A(XMLData)
         Cross Apply (
                        Select Field = a.value('local-name(.)','varchar(100)')
                              ,Value = a.value('.','varchar(max)') 
                         From  A.XMLData.nodes('/row')  as C1(n)
                         Cross Apply C1.n.nodes('./@*') as C2(a)
                         Where a.value('local-name(.)','varchar(100)') not in ('Column1','Column2')
                     ) C
     ) A

Updated Template

Your package has been delivered to 
Jane Doe
777 SV Lane
San Francisco CA, 94016
base64..

If it helps with the visualization, the sub-query is a "dynamic" unpivot and generates the following:

Field     Value
addCity   San Francisco
addLine   777 SV Lane
addSt     CA
addZip    94016
fName     Jane
lName     Doe
month     June
img       base64..   -- (presumably would be the image)

@KVNA Always happy to help – John Cappelletti Jun 12 '18 at 16:29 — John Cappelletti, Jun 12 '18 at 16:29

Create String Template from two stored procedures

1 Answers1