Scalar Function returns only one char

Question

Post reply

Scalar Function returns only one char

meelan

SSC Eights!

Points: 997
More actions
September 25, 2011 at 11:36 am

#99483

Hi all
I have a simple function as below:
ALTER FUNCTION [dbo].[TransactMonth]
(
@PostingDate datetime
)
RETURNS nvarchar
AS
BEGIN
DECLARE @ResultVar nvarchar (max)
SET @ResultVar = upper(SUBSTRING(datename(month,@PostingDate),1,3))+'-'+DATENAME(year,@PostingDate)
RETURN @ResultVar
END
When executed like "select dbo.transactmonth(getdate())", I am expecting a string as "SEP-2011". But, in SSMS, i see only a single character "S". Probably the first character.
How do i create a function to return the full string?
TIA

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

RETURNS nvarchar

unless you explicitly state a size, the default is 1;

it's only when you are using cast or convert that the unstated size is 30

it's pretty common to assume mix up when the default value is 1 or 30, so it's always best practice to define the size.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Gosh, NO!!!! You don't need to make a slow-as-molasses UDF for such a thing. It's just not necessary. Use the following, instead (where "SomeDate" would be the column of data you want to change the display of)...

SELECT SUBSTRING(CONVERT(VARCHAR(30),SomeDate,106),4,30)

FROM YourTable

The really cool thing about this is, not only is it blazingly fast and simple to boot, it will handle ANY language.

Please see the following article for more information on this subject...

http://qa.sqlservercentral.com/articles/formatting/72066/

--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)
Intro to Tally Tables and Functions

meelan SSC Eights! Points: 997 More actions · Answer 3

Lowell

Thank you for the reply. I will check on this matter deeply as I have seen the same issue in few SPs earlier

Jeff Moden:

I thought a UDF will improve performance. Thank you for educating me a very important point. I am no expert on TSQL. I now feel that many of my previous queries should be changed to remove UDFs for such a simple conversions.

Regards

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

meelan (9/25/2011)
Lowell
Thank you for the reply. I will check on this matter deeply as I have seen the same issue in few SPs earlier
Jeff Moden:
I thought a UDF will improve performance. Thank you for educating me a very important point. I am no expert on TSQL. I now feel that many of my previous queries should be changed to remove UDFs for such a simple conversions.
Regards

With few rare exceptions udf actually slow your code. Usually slows it WAY down.

I like the code reusability part as well but I must say that I was very few functions in prod atm.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

meelan (9/25/2011)
Lowell
Thank you for the reply. I will check on this matter deeply as I have seen the same issue in few SPs earlier
Jeff Moden:
I thought a UDF will improve performance. Thank you for educating me a very important point. I am no expert on TSQL. I now feel that many of my previous queries should be changed to remove UDFs [font="Arial Black"]for such a simple conversions.[/font]Regards

Thanks for the feedback Meelan. To be sure, not all UDF's are bad and even some of the bad ones can be useful if they're written correctly.

However, most Scalar and Multi-line UDF's (mTVF) that refer to information from a table are usually pretty tough on performance. Most Scalar and Multi-line UDF's that write to Table Variables are usually pretty tough on performance, as well. It IS sometimes worth the hit on performance to get everyone doing something the same way especially if it helps them avoid the loop. Still, it would be better to teach the team how to do things the right way and that there are exceptions to every rule of thumb.

Using Inline Table Valued Functions (iTVF) are much like views and, depending on how they're written, can either add extreme value and performance to code or crush it all just like any poorly written view might.

As a bit of a side bar, one of the keys to any computer language is learning what all of the functions do and how you can use them in unexpected ways. For example, a lot of folks jump through some flaming hoops to build functions that will strip the time from a date to create a "whole" (midnight time) DATETIME using conversions to VARCHAR. Converting anything with a numeric base to or from VARCHAR takes relatively a whole lot of time and then they put it in a Scalar UDF which takes still more time. It's much easier and faster to use some of the built in functions in "unexpected ways". For example, one of the fastest ways to create "whole dates" from something that has a non-midnight time can be done simply using CAST(DATEDIFF(dd,0,somedatetime) AS DATETIME). Once learned, it's easy to remember and blows the doors off of most any Scalar UDF to accomplish the same task.

--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)
Intro to Tally Tables and Functions