Build comma list from parameters

[tshad]

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

I am trying to get the string to show:

'Zero rows when: @Name = Main, @Number = 2, @ClassName= AnotherClass'

Thanks,

Tom

 

[markros]

In the upcoming version of SQL Server (code name Denali) it will be a piece of cake with the new concat function

http://blogs.lessthandot.com/index....ing/MSSQLServer/concat-function-in-sql-server

Until then, you will need to use CONVERT function to convert non character parameters to character.

Also, I suggest to add

set @ErrorMessage = ''

before running the second statement, so we don't need all COALESCE around @ErrorMessage

PluralSight Learning Library

 

[Qik3Coder]

You need to add an ELSE '' END or else you will get a null, which is clearing out your variable when you skip entering in a parameter.

Lod

You've got questions and source code. We want both!

 

[bborissov]

I would do this that way:

DECLARE @Name varchar(100),
    @Number int,
    @ClassName varchar(100),
    @ErrorMessage varchar(2000)
        
SELECT  @Name  = 'Main',
        @Number  = 2,
        @ClassName  = 'AnotherClass'

SELECT @ErrorMessage =  'Zero rows when: @Name = ' 
                                       + COALESCE(@Name,' NULL')
                    +', @Number = '    + COALESCE( CONVERT(varchar(10),@Number),' NULL')
                    +', @ClassName = ' + COALESCE(@ClassName,' NULL')

SELECT @ErrorMessage

That way you will see which parameters are passed and which does not have values.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.