Join Column Values

--------------------------------------------------------------------------------------
IF EXISTS (select * from sysobjects where id = object_id('usp_Join') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.usp_Join
GO

CREATE  PROCEDURE dbo.usp_Join( @vcColumnName varchar(100),
@vcFromvarchar(1000),
@vcWherevarchar(2000),
@bQuoteValuesbit,
@vcJoinString   varchar(8000) OUTPUT,
@vcDelimitervarchar(20) = ','


)
/**************************************************************************
DESCRIPTION:  Concatonates table column into a delimited string

PARAMETERS:
@vcColumnName- the column to be joined
@vcFrom- table from where data will be extracted
@vcWhere- filter conditions
@bQuoteValues- 1 = quote values, 0 = no quotes
@vcJoinString- Concatonated output of procedure
@vcDelimeter- Optional. Character(s) that will separate column values. 


USAGE:  
DECLARE   @vcJoinStrvarchar(8000)
     EXECUTE dbo.usp_Join 'au_lname', 
'pubs.dbo.authors',
'au_lname like ''d%''', 
1, 
@vcJoinStr OUTPUT,
'|---|'
SELECT @vcJoinStr


AUTHOR:Karen Gayda

DATE: 10/30/2003

MODIFICATION HISTORY:
WHODATEDESCRIPTION
-------------------------------------------------

***************************************************************************/
AS
BEGIN
DECLARE@nvcSQLnvarchar(4000),
@nvcParmDefnvarchar(500)


SET @nvcSQL = N'SET @vcJoinStringOUT='''' SELECT @vcJoinStringOUT = @vcJoinStringOUT + ' +
      'CASE WHEN ' + CAST(@bQuoteValues as varchar) + ' = 1 
      THEN ''' + @vcDelimiter + '''+CHAR(39)+CAST(' + @vcColumnName + ' as varchar)+CHAR(39)' +
' ELSE ''' + @vcDelimiter + '''+CAST(' + @vcColumnName + ' as varchar)' + 
' END ' +
'FROM (SELECT DISTINCT ' +  @vcColumnName + ' FROM ' + @vcFrom +
' WHERE ' + @vcWhere + ') t'

SET @nvcParmDef = N'@vcJoinStringOUT varchar(8000) OUTPUT'
EXECUTE sp_executesql @nvcSQL, @nvcParmDef, @vcJoinStringOUT=@vcJoinString OUTPUT

SET  @vcJoinString = RIGHT( @vcJoinString,LEN( @vcJoinString) - Len(@vcDelimiter))
        RETURN(0)

END