Assigning the result to a variable

Question

Assigning the result to a variable

imani_technology

SSCrazy Eights

Points: 8200
More actions
June 1, 2010 at 4:30 pm

#225212

I want to create an Execute SQL Task containing the following code:
[font="Arial"]SELECT COUNT(*)
FROM dbo.vwFileListForInsert
UNION
SELECT COUNT(*)
FROM dbo.vwFileListForUpdate[/font]
The result should be a single integer value. How can I assign that result to a variable that has a package-wide scope? Does it need to be stored in a variable with an Object datatype, or can it be an integer? If it is an Object datatype, how can I do something like the following:
If varCount > 0 Then. . .
Can an Object datatype support that type of activity?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

The only way that this query is going to return a single integer value is if both queries return the same value... and that's because of the UNION (vs. UNION ALL) statement. Otherwise, you will get two numbers back.

What you want to run is:

;WITH CTE AS

(

SELECT Qty = COUNT(*)

FROM dbo.vwFileListForInsert

UNION ALL

SELECT COUNT(*)

FROM dbo.vwFileListForUpdate

)

SELECT Qty = SUM(Qty)

FROM CTE

If you don't understand what's going on here, look up "WITH" in BOL.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 2

Thanks, that helps me to retrieve the proper number. However, I'm still confused on how to assign that result to a variable so that I can eventually add the condition If var > 0 then . . .

WayneS SSC Guru Points: 95460 More actions · Answer 3

Did you try clicking the "Help" button on the Execute SQL Task Dialog box after going to the Result Set tab?

It goes to something like this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

imani_technology (6/1/2010)
Thanks, that helps me to retrieve the proper number. However, I'm still confused on how to assign that result to a variable so that I can eventually add the condition If var > 0 then . . .

Set the ResultSet property in the General tab to Single Row.

Then, go the the ResultSet tab and map the index 0 to your variable.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP