A few
weeks ago I got a message with a question from a reader. The reader
had a performance problem with a UDF and asked if I would take a
look at it. Here's the UDF:
CREATE
FUNCTION
udf_CodeInsert
(
p_name_table
IN
TABLE_REFERENCE.name_table%TYPE
,
p_code_detail
IN
TABLE_REFERENCE.code_detail%TYPE
,
p_type_code
IN
VARCHAR2
)
RETURN
TABLE_REFERENCE.name_groupe%TYPE
-- -------------------------------------------------------------
-- Description: This function returns the group name or the
-- detail name detail code wanted
-- Parameters
-- In : p_name_table
-- p_code_detail
-- p_type_code - 2 possible choices:
-- G - groupe name,
-- D - detail name
-- Out: Nothing
-- Code:
-- -------------------------------------------------------------
IS
v_code
TABLE_REFERENCE.nom_groupe%TYPE;
v_type_code
VARCHAR2(1)
:=
UPPER(
p_type_code
);
BEGIN
IF
v_type_code
=
'G'
THEN
SELECT
t1.nom_groupe
INTO
v_code
FROM
TABLE_REFERENCE
t1
WHERE
t1.name_table
=
p_name_table
AND
t1.code_detail
=
p_code_detail
;
ELSIF
v_type_code
=
'D'
THEN
SELECT
t1.nom_detail
INTO
v_code
FROM
TABLE_REFERENCE
t1
WHERE
t1.name_table
=
p_name_table
AND
t1.code_detail
=
p_code_detail
;
END
IF;
RETURN
v_code;
EXCEPTION
WHEN
OTHERS
THEN
Pkg_Err.affiche_et_arret(
err_in
=>
SQLCODE
,
msg_in
=>
'HQ_REF2: '
||
SQLERRM
)
;
END
udf_CodeInsert;
As
I began looking at the code it was both familiar and strange.
What's that percent sign doing there? AND WHAT ABOUT
THOSE SEMICOLONS?
I don't use semicolons in T-SQL!
It
took a minute to register. Then it hit me. It's Oracle's PL/SQL
not T-SQL. I use Oracle every once in a while but I guess it's been
longer than I thought.
Unfortunately for the reader, I couldn't be much to help. All I
could offer were general suggestions about checking how often the
function was called and if the row counts for the table in the
SELECT had grown.
There are many differences between the syntax of T-SQL and PL/SQL.
The type declarations, EXCEPTION, and package support
are all examples of PL/SQL specific syntax used in the UDF. There
are many more syntax differences that don't appear in the function.
A
syntax element that isn't different is the semicolon. Both T-SQL
and PL/SQL variants on SQL allow the use of semicolons to separate
SQL statements. Even ACCESS's version of SQL allows them. The book
that I look to for SQL standards is A Guide to THE SQL STANDARD
, 4th edition by C. J. Date and Hugh Darwen. It doesn't
seem to mention the use of semicolons but they're used in the book's
examples as statement terminators.
Although I don't use them semicolons are a valid statement separator
in T-SQL. Try the sample batch that has two SQL statements on one
line:
SELECT 1
SELECT 2
Now try it with
semicolons at the end of each statement:
SELECT 1;
SELECT 2;
There's no difference
in the result. You may use semicolons between statements but unlike
C, there isn't a null statement so a statement like this isn't
acceptable:
BEGIN;
SELECT 1;
SELECT 2; END
(Results)
Server: Msg 170, Level
15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
Server: Msg 156, Level 15, State 1, Line
1
Incorrect syntax near the keyword 'end'.
If you take away the
semicolon after BEGIN everything is okay.
The following scalar
UDF, udf_SQL_StartDT, is taken from
Volume 1 Issue 11 of the T-SQL UDF of the Week Newsletter that
is the precursor to this newsletter. I've added semicolons
everyplace where I found that they were allowed:
ALTER FUNCTION
dbo.udf_SQL_StartDT
()
RETURNS datetime
-- Date/time the SQL Server Instance
started
/* Returns the data/time that the SQL Server instance was started.
*
* Common Usage:
select dbo.udf_SQL_StartDT() as [System Started AT]
*
* © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
*
* Published as Vol 1 #11 of The T-SQL UDF of the Week Newsletter
http://www.NovickSoftware.com/UdfofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN
DECLARE
@WorkingVariable
datetime;
SELECT
@WorkingVariable =
login_time
FROM
master..sysprocesses
WHERE
cmd='LAZY WRITER';
Return
@WorkingVariable;
END
All other places where I though of putting a
semicolon were rejected with syntax errors. Among the spots where a
semicolon isn't allowed are:
- Before or after the RETURN clause
- After the BEGIN statement
- Between AS and BEGIN
- After the END statement
- After a semicolon
In short semicolons are only allowed between
statements.
My article
last week's was on my formatting convention, which I call Separator
First Formatting (SFF). SFF makes it easy to add or remove
statements and statement clauses because of the placement of the
keywords and other separators at the beginning of lines of text.
Semicolons would only gum up the works and make it more difficult to
add or remove a clause by adding a double dash comment. For example
in the SELECT statement in the UDF
above, adding an ORDER BY clause on it's own line would require
moving the semicolon. SFF works because you don't have to move
language syntax around.
The only place where I've used semicolons in
T-SQL over the past four years is when I'm sending a multi-statement
batch from ADO or ADO.Net. Because I don't usually embed
carriage returns or line feeds in the SQL string, the semicolons
serve as
visual separators that make the SQL read more easily in the code and
in the SQL Profiler.
So I ask you, do you use semicolons in T-SQL?
Maybe in stored procedures or batches? Or someplace else? I'd like
to hear from those of you who do. I'll summarize the results in a
follow-up article. Please contact me at
coding-in-sql@novicksoftware.com.
Transact-SQL User-Defined Functions has been published! Take a look at it
now!
Do you have an interesting stored procedure, user-defined-function,
or other sample code? If you'd like to share them please send
them in an email to:
coding-in-sql@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andy
Andrew Novick
