passing a variable to an aggregate function

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

General SQL Server Forums

New to SQL Server Programming

passing a variable to an aggregate function

Author

Topic

zwheeler
Starting Member

25 Posts

Posted - 2013-08-29 : 16:37:38

Hi

I have cursor that loops through a table (the table only contains columnnames of several tables) the cursor has a variable declared
@columnname

when i run the following it works fine

select @columnname,0,0,0,0
from temp_prt

it gives me my expected output

mtr_5120,0,0,0,0
mtr_3247,0,0,0,0
mtr_5160,0,0,0,0
etc........

now i want to get the min of each column name like so

select @columnname,min(mtr_5120),0,0,0
from temp_prt ------> this works for min(mtr_5120)
mtr_5120,34.5,0,0,0

now I want to generalize so I try to pass in the variable name and I
do the following

select @columnname,min(@columnname),0,0,0
from temp_prt

(the columname (@columnname) exists in the table temp_prt)

but now i get an error
Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to decimal.

how can i pass the colunmame into the min and max functions or is that at all ppossible. I also tried the following:

select @columnname,'min(' + @columnname + ')',0,0,0
from temp_prt

but i get the same error
Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to decimal.

Any help would be greatly appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-29 : 16:50:01

You will need to use dynamic SQL. The dynamic SQL bible: http://www.sommarskog.se/dynamic_sql.html

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zwheeler
Starting Member

25 Posts

Posted - 2013-08-29 : 18:22:54

thank you

here is my solution

--define my outer loop
DECLARE @getName CURSOR,
@columnname varchar(35),
@sql nvarchar(2000);

SET @getName = CURSOR FOR
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'temp_prt'
order by column_name
OPEN @getName
FETCH NEXT
FROM @getName INTO @columnname
WHILE @@FETCH_STATUS = 0
BEGIN
--this is my inner loop

DECLARE @getMetric CURSOR,
@column_name varchar(35),
@metric_min decimal(12,3),
@metric_max decimal(12,3),
@metric_avg_mean decimal(12,3),
@metric_median decimal(12,3);
select @sql= N'SET @getMetric = CURSOR FOR select '
+ N' + min(' + @columnname + N' ),'
+ N' + max(' + @columnname + N' ),'
+ N' + avg(' + @columnname + N' ),'
+ N'((SELECT MAX(' + @columnname + N') FROM (SELECT TOP 50 PERCENT ' + @columnname
+ ' FROM temp_prt ORDER BY ' + @columnname + N') AS BottomHalf) + (SELECT MIN(' + @columnname
+ ') FROM (SELECT TOP 50 PERCENT ' + @columnname
+ ' FROM temp_prt ORDER BY ' + @columnname
+ N' DESC) AS TopHalf)) / 2.0'

+ N' from temp_prt '
+ N';OPEN @getMetric';

exec sp_executesql @sql,N'@getMetric cursor output',@getMetric output
FETCH NEXT
FROM @getMetric INTO @metric_min,@metric_max,@metric_avg_mean,@metric_median
WHILE @@FETCH_STATUS = 0
BEGIN
insert into prt_metric_test
(tag_column_name,mtr_min,mtr_max,mtr_avg,mtr_median)
values
(@columnname,@metric_min,@metric_max,@metric_avg_mean,@metric_median)

FETCH NEXT
FROM @getMetric INTO @metric_min,@metric_max,@metric_avg_mean,@metric_median
END
CLOSE @getMetric
DEALLOCATE @getMetric

--Close my outer loop
FETCH NEXT
FROM @getName INTO @columnname
END
CLOSE @getName
DEALLOCATE @getName

select * from prt_metric_test

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources