My first function - 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

General SQL Server Forums

New to SQL Server Programming

My first function

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2007-05-01 : 12:44:25

I'm using a UDF for the first time and I'm not sure if it's correct and how to call it. I want to return 'Yes' or 'No' for every industry value. My select statement passes 2 Int values to the UDF - the % Yes and %No. If %Yes > %No, then I want my function to return 'Yes' Else 'No'.

SELECT Industry,
100* SUM(CASE when BdOutsideMaj = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [Yes],
100* SUM(CASE when BdOutsideMaj = 'no' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [No]
FROM TCompanies
GROUP BY Industry
ORDER BY Industry

CREATE FUNCTION dbo.benchmarkerBdOutsideMaj(@Yes Int, @No Int)
RETURNS varchar(10)
AS
BEGIN
DECLARE @MyOutput varchar(10)
If @Yes > @No
SET @MyOutput = 'Yes'
Else
SET @MyOutput = 'No'
RETURN @MyOutput
END

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-01 : 12:47:15

your UDF by itself looks fine. am not sure how you want to use it in your SELECT statement?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

evanburen
Posting Yak Master

167 Posts

Posted - 2007-05-01 : 12:59:44

Ideally, I would like the results to look like this:

Industry
Yes
No
Overall (the Yes/No) value returned by the function benchmarkerBdOutsideMaj)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-01 : 13:02:07

From your query it looks like you are doing some computation to calculate if its a Yes or a No. But the function doesnt include any calculation part. So unless you are moving the calcualtion to the function too, it doesnt help to use the function just to decide a Yes or No based on 2 values. You might as well do it in a CASE statement within the query. Function calls on large resultsets can be expensive.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

evanburen
Posting Yak Master

167 Posts

Posted - 2007-05-01 : 13:10:33

If I do this, then I get the error

Invalid column name 'Yes'.
Invalid column name 'No'.

SELECT Industry,
100* SUM(CASE when BdOutsideMaj = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [Yes],
100* SUM(CASE when BdOutsideMaj = 'no' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [No],
(CASE when [Yes] > [No] then 'yes' else 'no' end) As [Summary]
FROM TCompanies
GROUP BY Industry
ORDER BY Industry

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-01 : 13:12:23

can you post some sample data in your TCompanies table?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

evanburen
Posting Yak Master

167 Posts

Posted - 2007-05-01 : 13:19:00

CompID Industry BdOutsideMaj
12931 Computer Hardware No
12932 Retail Yes
12933 Chemicals Yes
12934 Financial Services Securities Yes
12935 Printing & Publishing No

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-01 : 13:24:43

Its complaining about the invalid column names because (1) the columns dont exist in the table (2) the values are calculated at run time. So SQL Server has no knowledge about those 2 columns until runtime. so its giving a parse error. You need to replace the column names with the column definition.


SELECT Industry, 
(100* SUM(CASE when BdOutsideMaj = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID)),
100* SUM(CASE when BdOutsideMaj = 'no' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [No],
Case when (100* SUM(CASE when BdOutsideMaj = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID)) > (100* SUM(CASE when BdOutsideMaj = 'no' then 1 else 0 end) / COUNT(DISTINCT CompID)) 
	Then 'Yes' Else 'No' End as Summary

FROM TCompanies
GROUP BY Industry
ORDER BY Industry

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

evanburen
Posting Yak Master

167 Posts

Posted - 2007-05-01 : 13:30:31

That's fantastic! Thanks a bunch.

Not to be greedy, but is there a way to get my the name of 'Yes' column back. Now in QA it says 'No column name' for the % 'Yes'.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-01 : 13:34:47

Just add the column alias:

SELECT Industry,
(100* SUM(CASE when BdOutsideMaj = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID)) AS [Yes],

SQL Server Helper
http://www.sql-server-helper.com

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-01 : 13:34:53

sure, you can put them back..it should work.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

evanburen
Posting Yak Master

167 Posts

Posted - 2007-05-01 : 13:45:40

Thanks very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources