I am often tasked with making modifications to existing UIs, and tend to try and limit the impact I have by pushing a lot of the formatting and BL into the database.  One piece of code that I regularly have to look up is the code to convert a result set into a comma delimited list.  Here is the SQL 2005/2008 code that I just created for use in a client environment:

CREATE FUNCTION [dbo].[fn_LinkedString]
(
	-- Add the parameters for the function here
	@ID varchar(10)
)
RETURNS nvarchar(1000)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result nvarchar(1000)

	-- Add the T-SQL statements to compute the return value here
	SELECT @Result= x.List
	FROM
		(
			SELECT
				L1.ID,
				List = SUBSTRING((SELECT (', '+ LinkedID)
										FROM Linked L2
										WHERE
											L1.ID = L2.ID
										ORDER BY
											ID,
											LinkedID
										FOR XML PATH('')
										), 3, 1000) FROM Linked L1
			GROUP BY ID
		) x
	WHERE
		x.ID = @ID
	-- Return the result of the function
	RETURN @Result

END

I wrapped the code in a scalar valued function to make it easier to use and to keep the code clean when used.

SQL Server · TSQL

I decided that it would be fun to automate the creation of good history tables since that is something that many people leave until the end, or skip all together. I am going to start with a simple case and try to push through into Triggers and automatic Stored Procedure creation. To begin with, let’s take a look at the sp_help command and it’s results. sp_help is useful in looking at the details of objects, user defined types, and sql types. I am using the SQL Server Books Online from MSDN (http://doc.ddart.net/mssql/sql70/sp_help.htm) for reference.

A call to sp_help with no arguments will return a list of all objects, their owner, and their type. For example, calling sp_help on the master database of a SQLExpress returns 1831 rows, beginning with:

I will be ignoring the second table for the foreseeable future, and concentrating on the first. I am going through the top list and getting the information about all ‘user table’ rows and presenting it in a ListBox. To help accomplish this, I have created a class SP_HelpResults with a constructor that accepts a DataSet. It then uses the number of tables returned along with the column names to determine what type of results the DataSet represents and parses it into usable objects. Once I have the objects describing an individual table, I will begin constructing the new table and Triggers. I will publish more details as the project continues.

Automation · SQL Server