UDF using EXISTS help requested

CK

I have this UDF

CREATE FUNCTION IsSupervisor (@empID int)

RETURNS bit

AS

BEGIN

DECLARE @retval bit

SET @retval = 'FALSE'

IF EXISTS ( SELECT SupervisorID FROM Supervisors WHERE SupervisorID =
@empID )

SET @retval = 'TRUE'

RETURN @retval

END

This works ok. My question is "is there a better way to write this
function?" Can I just return the results of the Exists statement? Instead of
creating and setting a variable? If there is a row I want to return true,
else return false. Any comments are appreciated.

TIA,

~ck

Sep 10 '08 #1

Plamen Ratchev

You can write the function without using a variable:

CREATE FUNCTION IsSupervisor (@empID INT)
RETURNS BIT
AS
BEGIN
RETURN CASE WHEN EXISTS(SELECT *
FROM Supervisors
WHERE SupervisorID = @empID)
THEN 1
ELSE 0
END;
END

Note the BIT data type can have values of 1, 0 and NULL, but not
'True'/'False'.

--
Plamen Ratchev
http://www.SQLStudio.com

Sep 10 '08 #2

Erland Sommarskog

CK (c_**********@hotmail.com) writes:

I have this UDF

CREATE FUNCTION IsSupervisor (@empID int)

RETURNS bit

AS

BEGIN

DECLARE @retval bit

SET @retval = 'FALSE'

IF EXISTS ( SELECT SupervisorID FROM Supervisors WHERE SupervisorID =
@empID )

SET @retval = 'TRUE'

RETURN @retval

END

This works ok. My question is "is there a better way to write this
function?" Can I just return the results of the Exists statement?
Instead of creating and setting a variable? If there is a row I want to
return true, else return false. Any comments are appreciated.

The best is to not write the function at all, but use the expression
inline. Maybe in some future version there will be inline scalar functions,
but until then, best practice is to avoid dataaccess in UDFs.

Say that you run this query:

SELECT ...
FROM Employees
WHERE dbo.IsSupervisor(EmpId) = 1

SQL Server will have to scan Employees and call the UDF for every single
row.

If you instead write:

SELECT ...
FROM Employees E
WHERE EXISTS (SELECT *
FROM Supervisors S
WHERE E.EmpID = S.SupervisorID)

The optimizer have the choioce of reading the (supposedly) smaller
Supervisor table, and the look up the rows in the Employees table
through the index.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Sep 10 '08 #3

--CELKO--

1) Read what Erland said about performance. That is not the worst of
it.

2) You have no idea how to program in SQL, so you mimic what you do
know -- procedural code. We don't use the proprietary BIT data type
in good SQL. This is a declarative predicate language. We also don't
have BOOLEANs in SQL so assigning BIT (a proprietary numeric data
type) a string value is a pretty awful conceptual error.

3) This was not good practices in procedural code either -- do you
remember the types of coupling from Basic Software Engineering? This
is not fancy RDBMS stuff; this is foundations!

4) Why are Supervisors in their own table? That says they are not the
same kind of entity as an employee. Yet they are identified by an
emp_id. That means your data model is wrong; look up attribute
splitting. Do you also have a MalePersonnel and FemalePersonnel
table? Why not use weight and have FatPersonnel and ThinPersonnel?
This needs a re-design and not a kludge.

Sep 11 '08 #4

CK

Awesome. Thanks. the supervisor data is from a view by the way.It is not in
its own table.

"--CELKO--" <jc*******@earthlink.netwrote in message
news:0c**********************************@t54g2000 hsg.googlegroups.com...

1) Read what Erland said about performance. That is not the worst of
it.

2) You have no idea how to program in SQL, so you mimic what you do
know -- procedural code. We don't use the proprietary BIT data type
in good SQL. This is a declarative predicate language. We also don't
have BOOLEANs in SQL so assigning BIT (a proprietary numeric data
type) a string value is a pretty awful conceptual error.

3) This was not good practices in procedural code either -- do you
remember the types of coupling from Basic Software Engineering? This
is not fancy RDBMS stuff; this is foundations!

4) Why are Supervisors in their own table? That says they are not the
same kind of entity as an employee. Yet they are identified by an
emp_id. That means your data model is wrong; look up attribute
splitting. Do you also have a MalePersonnel and FemalePersonnel
table? Why not use weight and have FatPersonnel and ThinPersonnel?
This needs a re-design and not a kludge.

Sep 11 '08 #5

--CELKO--

>The supervisor data is from a view by the way. It is not in its own table. <<

That makes a little more sense. Is the determination of supervisor
versus non-supervisor really hard enough or used frequently enough to
justify a VIEW? I don't need a function if I have a VIEW -- think
declarative versus procedural coding.

Sep 11 '08 #6