Check Line Description User Defined Function

Ok, by now you've probably figured out that I'm a huge fan of user defined functions. Well, I am. Just this past week I had the need for yet another concatenation of rows thanks to Elite's bizarre handling of description fields. This one relates to check descriptions. Each GL line of a check has a description attached to it that can span multiple lines in the csddes table. So, the following udf will combine all the description lines for each bank / check / check line combination.

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfCsdDes]
(
@baid VARCHAR(8),
@cknum VARCHAR(8),
@ckline INT
)
RETURNS VARCHAR(4800) AS
BEGIN
 DECLARE @description VARCHAR(4800)

 SELECT @description = ''

 SELECT @description = @description + ' ' + des FROM csddes WHERE baid = @baid AND cknum = @cknum AND ckline = @ckline ORDER BY dsline

 RETURN LTRIM(RTRIM(@description))
END

You can call this udf in any query by passing the bankid, check number and check line variable, typically from the csddt table. Example will follow in the next post.