Friday, September 4, 2009

Matter Description User Defined Function

In Enterprise, the matter description is split among three fields; mdesc1, mdesc2 and mdesc3. While the mdesc1 field is often enough, many times we need the entire matter description in a report. The following user defined function (udf) automates the combination of the three fields.

Code to create the udf:

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfMattDesc]
(
    @mmatter VARCHAR(15)
)
RETURNS VARCHAR(185)
AS
BEGIN
  DECLARE @mattdesc VARCHAR(185)

   SELECT @mattdesc = mdesc1 + ' ' + ISNULL(mdesc2,'') + ' ' + ISNULL(mdesc3,'')
   FROM matter
   WHERE matter.mmatter = @mmatter

   RETURN RTRIM(@mattdesc)

END
This creates a udf named udfMattDesc that requires a value (mmatter) be passed to it and will return the full matter description of the specified matter. See the following example query to use the udf.
SELECT
matter.mmatter,
client.clname1,
dbo.udfMattDesc(matter.mmatter)
FROM matter
INNER JOIN client ON matter.mclient=client.clnum
WHERE matter.mclient='123456'

No comments:

Post a Comment