CAT | Uncategorized
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.
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.