Setting multiple variables from a Execute SQL Task result object with a single row

Question

I have the following sql in an EXECUTE SQL TASK:

SELECT [CnxnStrValue1] as INT_Support_CnxnStr
,[CnxnStrValue2] as Lawson_CnxnStr
,[CnxnStrValue3] as Lawson_HRIS_CnxnStr
FROM  [dbo].[InterfaceDBCnxn] 
WHERE InterfaceName = ?

The result set is set to an object variable. I also have three string variables to hold the values and typically I would map them to a For Each Loop Container. But, in this case, my result set will always only be one row because InterfaceName is the primary key of the table.

Whats is the best way to set the variables with out using a for each loop container?

billinkc · Accepted Answer · 2014-06-24 14:54:00Z

3

Change your result set from Full to Single Row. I use this pattern for my DW loads to get the surrogate key value for my unknown members.

enter image description here

ResultSet set to Single row

enter image description here

Map your parameters as needed. Here, I have 8 variables that get mapped

enter image description here

answered Jun 24, 2014 at 14:54

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

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

Comments

edhedges · Accepted Answer · 2014-06-24 14:47:14Z

-2

Given your table is Table and a column is Column_name and Column_name_two you can do something like this.

SELECT @yourVar = Column_name,
       @yourSecondVar = Column_name_two
FROM Table
WHERE Table_id = 1

answered Jun 24, 2014 at 14:47

edhedges

2,7282 gold badges31 silver badges63 bronze badges

3 Comments

bsivel Over a year ago

Do the parameter need to be set as output?

edhedges Over a year ago

I'm assuming you can just do DECLARE @yourVar type before the SELECT

bsivel Over a year ago

Sounds like this should work but I wen with billinkc's solution to keep it simple.

Collectives™ on Stack Overflow

Setting multiple variables from a Execute SQL Task result object with a single row

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related