I have some code that I wrote almost a year ago exactly (1/9/2013) and I would like to know if I wrote it well or if it can be improved. I don't have any fun input or output, as these are not set results coming out of this column in the table.
-- =============================================
-- Author: Malachi (Name Changed to Protect the possibly Guilty)
-- Create date: 1/9/2013
-- Description: Will give a list of the Bond Conditions
-- =============================================
CREATE FUNCTION [dbo].[fnBondConditionList]
(
@BondID int
)
RETURNS Varchar(MAX)
AS
BEGIN
DECLARE @Result Varchar(MAX)
SELECT @Result = (Select Justice.dbo.uCode.Description as BondConditions
FROM Justice.dbo.uCode INNER JOIN
Justice.dbo.xBondCondition ON Justice.dbo.uCode.CodeID = Justice.dbo.xBondCondition.ConditionID
WHERE Justice.dbo.xBondCondition.BondID = @BondID
FOR XML PATH (''))
SET @Result = replace(@Result, '<BondConditions>','')
SET @Result = replace(@Result, '</BondConditions>','<br />')
Set @Result = LEFT(@Result, LEN(@Result) - 1)
RETURN @Result
END
I cannot change the tables. Here are the table layouts:
uCode:
CREATE TABLE [dbo].[uCode](
[CodeID] [dbo].[CodeID] NOT NULL,
[CacheTableID] [dbo].[CacheTableID] NOT NULL,
[RevisionID] [dbo].[RevisionID] NOT NULL,
[Code] [dbo].[Word] NOT NULL,
[RootNodeID] [dbo].[NodeID] NOT NULL,
[EffectiveDate] [datetime] NULL,
[ObsoleteDate] [dbo].[ObsoleteDate] NULL,
[UserIDCreate] [dbo].[UserIDCreate] NOT NULL,
[TimestampCreate] [dbo].[TimestampCreate] NOT NULL,
[UserIDChange] [dbo].[UserIDChange] NULL,
[TimestampChange] [dbo].[TimestampChange] NULL,
[FilterState] [dbo].[StateCode] NULL,
[FilterSiteID] [dbo].[GUID] NULL,
[Description] [dbo].[DescriptionLong] NULL,
CONSTRAINT [PK_uCode] PRIMARY KEY NONCLUSTERED
(
[CodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IXuCode1] UNIQUE CLUSTERED
(
[CacheTableID] ASC,
[CodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
xBondCondition:
CREATE TABLE [dbo].[xBondCondition](
[BondID] [dbo].[BondID] NOT NULL,
[ConditionID] [dbo].[CodeID] NOT NULL,
CONSTRAINT [PK_xBonduCode] PRIMARY KEY CLUSTERED
(
[BondID] ASC,
[ConditionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This code was created to stuff everything from one column into a single column of a single record, which is then fed to a stored procedure that fills a datagrid.
Should I be looping this somehow (I get a bad taste in my mouth just typing that)?
I update this because I find myself using the same FOR XML PATH ('') Template for something very similar.
I cannot use CONCAT because I am using SQL SERVER 2008 (wouldn't that be nice).
With all that going into the Stored Procedure, the SPROC Renders something similar to this:
Where the rightmost column is the column of the SPROC that was produced by a function almost exactly the same as above.
