Problem with a simple Scalar UDF

Question

Problem with a simple Scalar UDF

Viewing 6 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

SQL Kiwi (12/30/2011)
drew.allen (12/30/2011)
Naming conventions are simply conventions. There is no right or wrong. As long as they are consistent and meaningful, it doesn't really matter exactly what convention you follow.
Exactly.

You condone Hungarian Notation?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Welsh Corgi (12/29/2011)
I'm having a problem with a simple Scalar UDF.
CREATE FUNCTION udf_ConvertDB2toSQL
RETURNS DateTime
AS
BEGIN
DECLARE @CSC_DATE varchar(7)
DECLARE @SQLDate Datetime
SET @CSC_DATE = '1111130'
SET @SQLDate = (SELECT CAST(SUBSTRING(@CSC_DATE, 4 ,2)+ '/' +
SUBSTRING(@CSC_DATE, 6 ,2) + '/' +
(CASE LEFT(@CSC_DATE, 1)
WHEN 0 THEN '19'
WHEN 1 THEN '20'
END) +
SUBSTRING(@CSC_DATE, 2 ,2) AS datetime) AS CSC_DateTime)
SELECT @SQLDate
END

I don't think you really need a function for this, you should be able to do the conversion as inline code:

select

a.*,

SQLDateTime =

convert(datetime,stuff(CSC_DATE,1,1,

case when CSC_DATE < '1' then '19' else '20' end))

from

( -- Test Data

select CSC_DATE = '0631130' union all

select CSC_DATE = '1111130' union all

select CSC_DATE = '1121215'

) a

Results:

CSC_DATE SQLDateTime

-------- -----------------------

0631130 1963-11-30 00:00:00.000

1111130 2011-11-30 00:00:00.000

1121215 2012-12-15 00:00:00.000

(3 row(s) affected)

Inline code will perform much better than a function. More info about that on the link below.

Demo Performance Penalty of User Defined Functions:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78601

Paul White SSC Guru Points: 150468 More actions · Answer 3

Jeff Moden (12/30/2011)
SQL Kiwi (12/30/2011)
drew.allen (12/30/2011)
Naming conventions are simply conventions. There is no right or wrong. As long as they are consistent and meaningful, it doesn't really matter exactly what convention you follow.
Exactly.
You condone Hungarian Notation?

I don't have very strong feelings either way; like code layout or the use of underscores, I adapt to whatever convention is in use at the place I happen to be working that day. I don't know what else to add: I was just agreeing with Drew's general statement, or at least what I took to be the sentiment behind it. If I was making a point about Hungarian Notation specifically, I would likely have quoted you instead. You make some good points, which many people will no doubt find to be persuasive reasons against.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 4

Michael Valentine Jones (12/30/2011)
I don't think you really need a function for this, you should be able to do the conversion as inline code...

This seems like exactly the sort of reusable code that ought to be in an in-line function (not a scalar function as already noted).

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Thanks everyone and have a happy and safe New Year. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 6

SQL Kiwi (12/30/2011)
Jeff Moden (12/30/2011)
SQL Kiwi (12/30/2011)
drew.allen (12/30/2011)
Naming conventions are simply conventions. There is no right or wrong. As long as they are consistent and meaningful, it doesn't really matter exactly what convention you follow.
Exactly.
You condone Hungarian Notation?
I don't have very strong feelings either way; like code layout or the use of underscores, I adapt to whatever convention is in use at the place I happen to be working that day. I don't know what else to add: I was just agreeing with Drew's general statement, or at least what I took to be the sentiment behind it. If I was making a point about Hungarian Notation specifically, I would likely have quoted you instead. You make some good points, which many people will no doubt find to be persuasive reasons against.

Heh... That's why I brought it up. I was curious of your thoughts on that specific subject.

In the real world, I do pretty much as you do... I adapt to whatever "standard" is in place for whomever I'm working for although I'll always make suggestions. Usually, things are just too far gone on existing systems for me to bring up other than to recommend that it should be avoided on new systems.

Shifting back to the original subject, although I prefer to avoid Hungarian Notation, I absolutely agree with you and Drew... pick a standard and enforce it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)