Wednesday, October 31, 2012

Define Default Parameter in User Defined Funciton

I have a user defined function like this.

CREATE FUNCTION [dbo].[fn_FormatCSVString]
(
    @inString varchar(200), @inFixedLength tinyint=2
)
RETURNS VARCHAR(300)
AS
BEGIN

    DECLARE @csvResult VARCHAR(300), @position smallint, @strLen smallint
    SET @strLen = LEN(@inString)
    SET @position=1
    SET @csvResult=''
    IF @strLen<=@inFixedLength OR @inFixedLength<=0
    BEGIN
        RETURN @inString
    END
    WHILE @position<=@strLen
    BEGIN
        SET @csvResult = @csvResult + SUBSTRING(@inString,@position,@inFixedLength)+','
        SET @position = @position+ @inFixedLength
    END

    RETURN SUBSTRING(@csvResult,1,LEN(@csvResult)-1)

END

Can you call it with second parameter ignored?
    SELECT [dbo].[fn_FormatCSVString]('abc123')

No, SQL Server won't allow you to do that. You will have to either specify default or  provide a meaningful parameter for that.
    SELECT [dbo].[fn_FormatCSVString]('abc123',default)
    SELECT [dbo].[fn_FormatCSVString]('abc123',3)

Do not know why MS does this differently from what they do on procedures. I guess this is something you have to remember. It can also be a tricky question in the DB interview to see if they are detail oriented.

No comments: