Sum dynamic columns - SQL Server Forums

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)

Sum dynamic columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-30 : 07:55:18

Rui Silva writes "Hi,

I wished to know if it's possible to sum a column that is passed do a stored procedures.

Example:

Create spblabla (@ColToSum varchar(10), @Value decimal(18,2) output)

as

select Value = sum(@ColToSum) from table.

Thanks for your attention."

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-30 : 08:18:21

Create spblabla (@ColToSum varchar(10), @Value decimal(18,2) output)
as
declare @j nvarchar(800), @i decimal(18,2)
set @j = 'select @i = sum(' + @ColToSum + ') from t'
exec sp_executesql @j, N'@i decimal(18,2) output', @i output

set @Value = @i

return

- Vit

dsdeming

479 Posts

Posted - 2003-07-30 : 08:22:51

It is possible, but you'll have to use dynamic SQL to do it.

USE Northwind
GO

DECLARE @sysColumnName sysname, @sysTableName sysname
SET @sysColumnName = 'Quantity'
SET @sysTableName = '[Order Details]'
EXECUTE( 'SELECT SUM(' + @sysColumnName + ') FROM ' + @sysTableName )

For more info, see sp_executesql and EXECUTE in BOL.

Dennis

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-30 : 08:54:30

Columns in your tables are static -- they shouldn't be changing unless you change your database. With stored procedures, if you make changes to your database, you will need to make changes to the stored procs.

The reason why I mention this is because you should avoid dynamic SQL and write the procedure this way:


create procedure SumColumn(@ColumnName varchar(100), @Value money output)
AS
 select @Value = CASE @ColumnName 
   WHEN 'Amount' THEN SUM(Amount)
   WHEN 'Hours' THEN SUM(Hours)
   WHEN 'Units' THEN Sum(Units)
   ELSE 0 END
 from
   SomeTable

because your columns in your tables do not change, a stored procedure in this format should suffice. If the columns do change, you will need to edit stored procs ANYWAY, so it makes sense.

Advantages:

1) this proc is fully compiled and optimizable, unlike dynamic SQL
2) passing in an incorrect column name doesn't produce an error
3) no dynamic SQL is needed, so there are no permissions issue
4) you seperate the logical layer from the physical -- maybe the table has all of it's columns renamed, but you can still map the old values to the new columns without having to change any code that calls the stored procedure. i.e., you can still pass 'Hours' but because of changes it now returns SUM(Hrs).

hope this helps

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources