Using scalar functions to concatenate strings

Posted by Jeff Rix on June 27, 2011 under T-SQL | Be the First to Comment

Create Function- Create function to accept Detention ID and return all comments concatenated for the Detention ID.

 

  SET NOCOUNT ON;

  USE Discipline;

 

–DROP FUNCTION

  IF OBJECT_ID(‘dbo.ConcatDetentionComments’, ‘FN’) IS NOT NULL

  DROP FUNCTION dbo.ConcatDetentionComments;

GO

 

–CREATE FUNCTION

  –CONCATENATE COMMENTS

CREATE FUNCTION dbo.ConcatDetentionComments

  (@detentionID INT) RETURNS VARCHAR(MAX)

AS

  BEGIN

  declare @COMMENTS AS VARCHAR(MAX)

  SET @COMMENTS = ”;

 

  SELECT @COMMENTS = @COMMENTS + COMMENT + ‘; ‘

  FROM Discipline.DBO.DETENTION_COMMENTS

  WHERE DETENTION_ID = @detentionID;

 

  RETURN @COMMENTS;

  END

GO

Test Results

–BEFORE DETENTION ID COMMENTS ARE CONCATENATED
SELECT DETENTION_ID, COMMENT
FROM Discipline.dbo.DETENTION_COMMENTS

–CONCATENATES COMMENTS WHERE MORE THAN ONE COMMENT EXISTS PER DETENTION ID
SELECT DISTINCT DETENTION_ID, dbo.ConcatDetentionComments(DETENTION_ID) AS COMMENTS
FROM Discipline.dbo.DETENTION_COMMENTS