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:
Post a Comment