Showing posts with label User defined Function. Show all posts
Showing posts with label User defined Function. Show all posts

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.