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 5 2645
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
CK (c_**********@h otmail.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.IsSuperviso r(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****@sommarsk og.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
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.
Awesome. Thanks. the supervisor data is from a view by the way.It is not in
its own table.
"--CELKO--" <jc*******@eart hlink.netwrote in message
news:0c******** *************** ***********@t54 g2000hsg.google groups.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.
>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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Geetha |
last post by:
I want to know the column names that exists in more than one table;
also, I want to know the table names where it exists. I tried this
query and it took a long time and so I stopped it:
select table_name,column_name, data_length, data_type from
user_tab_cols
where column_name in (select column_name from user_tab_cols
group by column_name
having count (*) > 1)
order by column_name
|
by: jennifer1970 |
last post by:
I need to insert records into the table parSalesDetailModifier from
OLDparSalesDetailModifier where (1) those records DO NOT exit in
parSalesDetailModifier and (2) those records have a parent record in
parSalesDetail.
When I run the below query I get the error message that I am violating
the Primary Key Constraint for parSalesDetailModifier. In other words,
it's trying to insert a record that does exist.
Also posted below are create...
|
by: MaRcElO PeReIrA |
last post by:
Hi there,
I was in troubles with a UPDATE+IN statement:
The following command use to take about 5 minutes to
be done:
UPDATE requisicao SET conclusao='3' WHERE reg IN
(SELECT reg FROM requisicao WHERE now()-data>'15
days');
|
by: Grasshopper |
last post by:
Hi,
I am automating Access reports to PDF using PDF Writer 6.0. I've
created a DTS package to run the reports and schedule a job to run this
DTS package. If I PC Anywhere into the server on where the job is
running, the job runs sucessfully, PDF files got generated, everything
is good. If I scheduled the job to run at the time that I am not
logged into the server, Access is not able to print to the printer.
The error is pretty...
|
by: Darin |
last post by:
I have a table that I want to delete specific records from based on
data in other tables. I'm more familiar with Access '97, but am now
using 2003, but the database is in 2000 format. In '97, I think I
could have easily done this using joins, but I kept getting "could not
delete from specified tables" errors. Some google searching has
indicated I need to use a subquery. After many failed attempts with
different approaches, I finally...
| |
by: MLH |
last post by:
Mr Leigh Purvis gave me a very clever piece of SQL to accomplish what
is probably an uncommon objective. In it, he uses the EXISTS operator.
I can find no documentation on it in A97 HELP. I would like to read
more about this useful SQL operator. Suggestions?
SELECT CustID, OutType, EXISTS (SELECT CustID FROM tblCorrespondence
AS tblC
WHERE tblC.CustID = tblCorrespondence.CustID AND tblC.OutType = "01")
|
by: Mr.KisS |
last post by:
Hello.
I'm under Windows XP PRO SP1, IIS 5.1 ans SQL SERVER 2005
Express. When i try to open a connexion with :
<connectionStrings>
<add name="AppCnxStr"
connectionString="Server=KLEO\SQLEXPRESS;Integrated
Security=True;Database=C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\wizou.mdf"
providerName="System.Data.SqlClient" />
|
by: Julia |
last post by:
Hi, there,
I am trying to append a binary file by using:
FILE *strean;
stream = fopen( "c:\temp.dat", "ba+" ));
Is there a way that I can check if the file exists or not before
fopen, because I will call different work flow by check the status of
|
by: desktop |
last post by:
I have read in Bjarne Stroustrup that using malloc and free should be
avoided in C++ because they deal with uninitialized memory and one
should instead use new and delete.
But why is that a problem? I cannot see why using malloc instead of new
does not give the same result.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |