[RESOLVED] Sum the values of two select statements

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 2000 Forums

Transact-SQL (2000)

[RESOLVED] Sum the values of two select statements

Author

Topic

devan
Starting Member

2 Posts

Posted - 2010-06-17 : 11:19:17

I've simplified my SP below to illustrate what I'm trying to do. Let's say I want to get the total number of employees from two different tables. The SP is written this way because the input parameters consist of one or more department codes such as 'A6', 'B3', 'F9', etc. For example, the SP could end with: IN ('B3','A9','R4').

When slightly modified, this SP works great when returning a list of names. But I've been unable to figure out how to "add" the two numeric values -- the returned value from @s1 + @s2.

CREATE procedure getEmployeeCount
@dept varchar(20)

as set nocount on;

declare @s1 varchar(100)
declare @s2 varchar(100)

set @s1 = 'SELECT COUNT (*) AS EmployeeCount
FROM tblBranch_A
WHERE DepartmentCode IN '

set @s2 = 'SELECT COUNT (*) AS EmployeeCount
FROM tblBranch_B
WHERE DepartmentCode IN '

I can get a numeric value from either of the statements below, but I haven't figured out a way to return the combined value.

exec(@s1+'('+@dept+')')
exec(@s2+'('+@dept+')')

=== UPDATE ===

I changed the query (see below) to use a SUM. It's sortta working, but the SP is returning two numeric values instead of one.

declare @dept varchar(20)
set @dept = '(''A6'')'

declare @s1 varchar(100)
declare @s2 varchar(100)

set @s1 = 'SELECT SUM(ActiveEmployee)
FROM tblBranch_A
WHERE DepartmentCode IN ' + @dept

set @s2 = 'SELECT SUM(ActiveEmployee)
FROM tblBranch_B
WHERE DepartmentCode IN ' + @dept

exec(@s1+@s2)

My application's ExecuteScalar command is seeing 48, but the SP is returning 48 and then 23. Query Analyzer shows two separate rows being returned.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-17 : 14:17:01

This is at best a hack..

exec('SELECT SUM(EmployeeCount) FROM (' + @s1+'('+@dept+')'+' UNION ALL '+@s2+'('+@dept+')' + ') T')

Maybe now is a good time to read about SQL Injection.

EDIT: Links worth having a look
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://sqlinthewild.co.za/index.php/2009/04/03/dynamic-sql-and-sql-injection/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-17 : 14:39:29

[code]declare @dept varchar(20)
set @dept = '(''A6'')'
declare @s1 varchar(1000)
set @s1 =
'select (SELECT SUM(ActiveEmployee) FROM tblBranch_A WHERE DepartmentCode IN ' + @dept +')
+ ( SELECT SUM(ActiveEmployee) FROM tblBranch_B WHERE DepartmentCode IN ' + @dept + ')'

exec(@s1)[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

devan
Starting Member

2 Posts

Posted - 2010-06-17 : 15:43:35

Thanks to each of you -- both suggestions worked. I had something nearly identical to webfred's solution:

'select (SELECT SUM(ActiveEmployee) FROM tblBranch_A WHERE DepartmentCode IN ' + @dept + ')'
+ '(SELECT SUM(ActiveEmployee) FROM tblBranch_B WHERE DepartmentCode IN ' + @dept + ')'

...but my code had the two extra quotes (as shown in red). When removed, I received the proper result in 1 vs. 2 records.

It's always necessary to consider the possibility of injection when building dynamic SQL. In this case, users are not manually entering any text. The parameters are built using dropdownlist values which are further examined by a server-side SQL filter.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources