Using input string as field name for AVG()

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

Transact-SQL (2005)

Using input string as field name for AVG()

Author

Topic

Surfer513
Starting Member

29 Posts

Posted - 2010-09-29 : 22:41:38

I have a T-SQL Stored Procedure, and in that SP I want to be able to take the AVG() of a certain field that is determined by an input parameter. Below is my current stored procedure, but I'm getting an error when I try to save it, so I believe I have a syntax/logic error. Any ideas how I can accomplish this?
Thanks in advance :)

CREATE PROCEDURE dbo.spCalcBillComp
	@strfield nvarchar(50),
	@avgbills decimal(10,2) OUTPUT,
	@numberentries int OUTPUT

AS
	/* SET NOCOUNT ON */

	SELECT @numberentries=COUNT(*), @avgbills=AVG(@strfield)
	FROM aspnet_tblUserBills
	WHERE aspnet_tblUserBills.@strfield > 0.00
	RETURN

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 23:23:10

What is the error & also what is this "aspnet_tblUserBills.@strfield" in the where clause?

PBUH

lazycoder
Starting Member

12 Posts

Posted - 2010-09-30 : 04:35:22

AVG function doesn't accept column name in variable a parameter. You can use CASE function instead:

SELECT @numberentries=COUNT(*),
	@avgbills =
	case @strfield
		when 'column1' then AVG(column1)
		when 'column2' then AVG(column2)
		else AVG(column3)
	end
FROM aspnet_tblUserBills
WHERE aspnet_tblUserBills.@strfield > 0.00

http://it.expertmonster.com/

Surfer513
Starting Member

29 Posts

Posted - 2010-09-30 : 20:01:13

quote:
Originally posted by lazycoder

AVG function doesn't accept column name in variable a parameter. You can use CASE function instead:

SELECT @numberentries=COUNT(*),
@avgbills =
case @strfield
when 'column1' then AVG(column1)
when 'column2' then AVG(column2)
else AVG(column3)
end
FROM aspnet_tblUserBills
WHERE aspnet_tblUserBills.@strfield > 0.00

http://it.expertmonster.com/

I see what you're saying about the case with the AVG() function, and I've modified accordingly, but I still get the error and I think it may be coming from my line that states "WHERE aspnet_tblUserBills.@strfield > 0.00"

Is that also incorrect syntax?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-01 : 00:06:49

You would have to use dynamic SQL for that: http://www.sommarskog.se/dynamic_sql.html

But why does the column name need to be dynamic? How many columns are there that you need to do this for?

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

Subscribe to my blog

lazycoder
Starting Member

12 Posts

Posted - 2010-10-01 : 02:18:29

quote:
Originally posted by Surfer513
I see what you're saying about the case with the AVG() function, and I've modified accordingly, but I still get the error and I think it may be coming from my line that states "WHERE aspnet_tblUserBills.@strfield > 0.00"

Is that also incorrect syntax?

Sorry, i didn't notice that in the WHERE clause. If I understand you correctly, then just use the CASE function the where clause too. So it would look like this:


SELECT @numberentries=COUNT(*),
	@avgbills =
	case @strfield
		when 'column1' then AVG(column1)
		when 'column2' then AVG(column2)
		else AVG(column3)
	end
FROM aspnet_tblUserBills
WHERE case @strfield
		when 'column1' then column1
		when 'column2' then column2
		else column3
	end > 0

-----------------
http://it.expertmonster.com/

lazycoder
Starting Member

12 Posts

Posted - 2010-10-01 : 02:24:14

You could also create different versions of the stored procedure, one for each column and call the appropriate as needed. But that's what you wanted to avoid. However, it would be best for performance.
Another option would be to have more versions of the statement in a single stored procedure, and use IF function to run the one you need.

If there are too many columns by which you need to vary, then you can use dynamic SQL as tkizer suggested.

-----------------
http://it.expertmonster.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources