Jason Mauss' Blog Cabin

Because someone's got to do the dirty work

Blog-Flair

Blogroll

Links

What's wrong with this T-SQL? #2

Hint: Today's question is pretty easy if you understand which SQL Server data type are valid for which operations with user-defined functions. What you want to build is a scalar-value function named GetBooleanTextFromInt that returns a boolean text string based on an integer passed to it. So if you pass 0 (zero) to it, it returns the text 'False'. Any other integer value passed to it should return the text 'True'. For example:

SELECT GetBooleanTextFromInt(0) AS 'BooleanText'

returns a single field named BooleanText with the text value 'False' in it. while

SELECT GetBooleanTextFromInt(12) AS 'BooleanText'

returns a single field named BooleanText with the text value 'True' in it.

Here is the initial function, try to spot the problem(s):

CREATE FUNCTION GetBooleanTextFromInt (@num1 int) 
RETURNS text
AS 
BEGIN
 IF @num1 = 0
 BEGIN
  RETURN 'False'
 END
 ELSE
  RETURN 'True'
END

 

Posted: Aug 30 2004, 03:53 PM by jamauss | with 6 comment(s)
Filed under: ,

Comments

Karl said:

A text locale variable is implictly created and assigned the value of true/false...neither having a text variable nor assigning to it is legal....and your udf has to end with a select...
# August 30, 2004 7:50 PM

Karl said:

bah..local
# August 30, 2004 7:50 PM

Karl said:

sob, has to end with a return...i give up...
# August 30, 2004 8:15 PM

Jason Mauss said:

Karl - I think you alluded to the right answer in your first response. You can't assign to a text variable which you're doing implicitly by

RETURN 'False'

and

RETURN 'True'

You also caught the fact that your udf has to end with the 'RETURN' statement.

good job.
# August 30, 2004 8:18 PM

Jerry Pisk said:

There's also another problem - your function (if it managed to return the text value) would return 'True' if you passed it NULL, which is probably not exactly what you would expect.
# August 30, 2004 10:24 PM

Jason Mauss said:

Here is - (including Jerry's good catch of the possibility of NULL being passed in) -what I would modify the original T-SQL to:

CREATE FUNCTION GetBooleanTextFromInt (@num1 int)
RETURNS char(5)
AS
BEGIN
DECLARE @ReturnText char(5)
IF (@num1 = 0)
BEGIN
SET @ReturnText = 'False'
END
ELSE
IF @num1 IS NULL
BEGIN
SET @ReturnText = 'False'
END
ELSE
If @num1 <> 0
SET @ReturnText = 'True'
RETURN @ReturnText
END
# August 30, 2004 11:09 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)