SQL Server Function not producing expected output

Question

I have written below function in SQL Server:

ALTER FUNCTION dbo.fnFormatMessage 
    (@Message varchar(max), @Thought varchar(max))
RETURNS varchar
BEGIN
    DECLARE @FinalMessage AS varchar(max) = ' '

    SELECT
        @FinalMessage = LTRIM(RTRIM(CASE WHEN LEN(RTRIM(LTRIM(CONVERT(VARCHAR(MAX),ISNULL(@Thought,''))))) > 0 THEN
                                CASE WHEN SUBSTRING(CONVERT(VARCHAR(MAX), ISNULL(@Message,'')), LEN(CONVERT(VARCHAR(MAX), ISNULL(@Message,''))) , 1) IN ('?','!', '.')THEN
                                    CONVERT(VARCHAR(MAX), ISNULL(@Message,''))
                                ELSE 
                                    CONVERT(VARCHAR(MAX), ISNULL(@Message,''))
                                END
                            + CHAR(13) + CHAR(13) + CHAR(10) +'My Thoughts: ' + CHAR(13) 
                            + CASE WHEN SUBSTRING(CONVERT(VARCHAR(MAX), ISNULL(@Thought,'')), LEN(CONVERT(VARCHAR(MAX), ISNULL(@Thought,''))) , 1) IN ('?','!', '.') THEN
                                CONVERT(VARCHAR(MAX), ISNULL(@Thought,''))
                            ELSE 
                                CONVERT(VARCHAR(MAX), ISNULL(@Thought,'')) + '.'
                            END
                    ELSE
                        CASE WHEN SUBSTRING(CONVERT(VARCHAR(MAX), ISNULL(@Message,'')), LEN(CONVERT(VARCHAR(MAX), ISNULL(@Message,''))) , 1) IN ('?','!', '.') OR 
                                                                                                                        CONVERT(VARCHAR(MAX), ISNULL(@Message,'')) = ''
                        THEN
                                CONVERT(VARCHAR(MAX), ISNULL(@Message,''))
                        ELSE 
                                CONVERT(VARCHAR(MAX), ISNULL(@Message,'')) + '.'
                        END 
                END)) 
                Return @FinalMessage
    END

When I run it as

 Select dbo.fnFormatMessage('This is a message', 'This is my thought')

I am expecting below output:

This is a message
My Thought: This is my thought

But it just returns T

RETURNS VARCHAR should be RETURNS VARCHAR(MAX). Thank you, that'll be $1000 in consulting fees. :-) — Jeroen Mostert
– Jeroen Mostert, Commented Oct 25, 2016 at 15:09
Break it down into smaller pieces and find the bug. You can do this. — Seth Flowers
– Seth Flowers, Commented Oct 25, 2016 at 15:09
Bad habits to kick : declaring VARCHAR without (length) - you should always provide a length for any varchar variables and parameters that you use — marc_s
– marc_s, Commented Oct 25, 2016 at 16:06

Lamak · Accepted Answer · 2016-10-25 15:10:01Z

4

Change the RETURNS varchar to RETURNS varchar(8000) or the appropriate length. Always use en explicit length to VARCHAR

answered Oct 25, 2016 at 15:10

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server Function not producing expected output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related