Attention: We are retiring the ASP.NET Community Blogs. Learn more >

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

 

4 Comments

  • 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...

  • sob, has to end with a return...i give up...

  • 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.

  • 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

Comments have been disabled for this content.