Help with assigning resultset to two variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Help with assigning resultset to two variables

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-22 : 14:06:29

I have a SQL task where I call a simple Stored Procedure. To keep things easy, lets just say that the SP does nothing other than update a couple tables.

I want to store the counts of these tables as two different variables, which are set in the SQL Task Result Set screen.

I can't seem to set up the SQL task to accept two variables in the Result Set. I can get it to work when using one variable, just not two.

How can I assign two differenct count values to my result set, storing each count as a separate variable?

Hopefully this makes sense.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-22 : 18:27:25

[code]
declare @count1, @count2

Select @count1 = sum(wakawaka),
@count2 = sum(wikiwiki)
FROM ?
[/code]

like this?

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2011-08-22 : 18:39:12

"I can't seem to set up the SQL task to accept two variables in the Result Set"

Perhaps you are using SET? as in:

SET @MyVariable = ...

Using SET you can only set one variable per statement. If you use SELECT instead of SET (as per yosiasz's example) then you can "set" multiple variables in one statement.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-23 : 07:42:23

Thanks for the replies... I am doing somethign similar to yosiasz suggestion. My issue is trying to assign each count from the selects to variables in the SSIS package via the SQL Task Result Set.

I have my SQL Task set to single row, which works when I am using one variable. When I try to set the second, the package fails. I tried changing the result set to full result set, but this also fails.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-23 : 07:43:08

Actually, I am using two separate select statements rather than one as yosiasz shows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 07:47:08

quote:
Originally posted by qman

Actually, I am using two separate select statements rather than one as yosiasz shows.

you need to use same select for both and return as a resultset or else you will not be able to map both resultsets to variables. it needs to a single resultset.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-23 : 08:14:30

Still failing...., this is driving me nutz.

SP:

declare @ProdCount int;
declare @TestCount int;

Select
@ProdCount = (select COUNT(*) from #AAA where PROD_TEST = 'P'),
@TestCount = (select COUNT(*) from #AAA where PROD_TEST = 'T')
from #retreivedRecords

RETURN

SSIS SQL TASK:

Result Set = Single Row
Result Set Name = @ProdCount Result Set Variable = xxx
Result Set Name = @TestCount Result Set Variable = bbb

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-23 : 09:25:08

Got it to work, thanks.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 09:26:47

no need of variables just use command like like


select COUNT(CASE WHEN PROD_TEST = 'P' THEN 1 ELSE NULL END) AS ProdCount,
COUNT(CASE WHEN PROD_TEST = 'T' THEN 1 ELSE NULL END)  AS TestCount 
from #AAA

and set properties as

Result Set = Single Row
Result Set Name = 0 Result Set Variable = xxx
Result Set Name = 1 Result Set Variable = bbb

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources