Build string from passed parameters

Question

Post reply

Build string from passed parameters

tshad

SSCertifiable

Points: 5905
More actions
August 29, 2011 at 2:04 pm

#242439

Is there to easily build a comma delimited list from parameters that are passed. I know you can do it with COALESCE and a table but not with parameters:
DECLARE @Name varchar(100),
@Number int,
@ClassName varchar(100),
@ErrorMessage varchar(2000
SELECT @Name = 'Main',
@Number = 2,
@ClassName = 'AnotherClass'
SELECT @ErrorMessage = 'Zero rows when: ' +
CASE WHEN @Name IS NULL THEN '' ELSE COALESCE(@ErrorMessage + ', ', '') + '@Name = ' + @Name END +
CASE WHEN @Number IS NULL THEN '' ELSE COALESCE(@ErrorMessage + ', ', '') + '@Number = ' + CONVERT(varchar(10),@Number) END +
CASE WHEN @ClassName IS NULL THEN '' ELSE COALESCE(@ErrorMessage + ', ', '') + '@ClassName = ' + @ClassName END
SELECT @ErrorMessage[/code]
I am trying to get the string to show:
'Zero rows when: @Name = Main, @Number = 2, @ClassName= AnotherClass'
Thanks,
Tom

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

This gets the results that you want.

DECLARE @Name varchar(100),

@Number int,

@ClassName varchar(100),

@ErrorMessage varchar(2000)

SELECT @Name = 'Main',

@Number = 2,

@ClassName = 'AnotherClass'

SELECT @ErrorMessage = 'Zero rows when: '

SELECT @ErrorMessage = CASE WHEN @Name IS NULL THEN @ErrorMessage ELSE COALESCE(@ErrorMessage , '') + '@Name = ' + @Name END

SELECT @ErrorMessage = CASE WHEN @Number IS NULL THEN @ErrorMessage ELSE COALESCE(@ErrorMessage + ', ', '') + '@Number = ' + CONVERT(varchar(10),@Number) END

SELECT @ErrorMessage = CASE WHEN @ClassName IS NULL THEN @ErrorMessage ELSE COALESCE(@ErrorMessage + ', ', '') + '@ClassName = ' + @ClassName END

SELECT @ErrorMessage

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

You only need 1 SELECT. Since you're also just checking for NULL values, you also don't need any CASE statements.

DECLARE @Name VARCHAR(100),

@Number INT,

@ClassName VARCHAR(100),

@ErrorMessage VARCHAR(2000)

;

SELECT @Name = 'Main',

@Number = 2,

@ClassName = 'AnotherClass'

;

SELECT @ErrorMessage = 'Zero rows when:'

+ ISNULL(STUFF(

ISNULL(', @Name = ' + @Name,'')

+ ISNULL(', @Number = ' + CAST(@Number AS VARCHAR(10)),'')

+ ISNULL(', @ClassName = ' + @ClassName,'')

,1,1,''),'')

;

SELECT @ErrorMessage

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tshad SSCertifiable Points: 5905 More actions · Answer 3

tshad

SSCertifiable

Points: 5905

August 29, 2011 at 9:33 pm

#1374804

That works fine.

Thanks,

Tom