magine that you’ve just finished building a database for another Web-based system. Now it’s time to start thinking about writing all that SQL to move the data in and out of the database. Being a security-conscious developer, you decide against building dynamic SQL in your data-tier code. You choose to do the right thing and write stored procedures (SPs). Daunting task, isn’t it?

Stored procedures are much safer than dynamic SQL statements because the data coming from outside of the application is not mixed up with SQL DML (data manipulation language) statements. SPs pass the data as parameters, providing clear separation from the code:

spMySampleProc (@par1 int, @par2 nvarchar(100))

Writing stored procedures is not everyone’s cup of tea because of the amount of work they require, however. Suppose you have 10 tables with a total of 100 fields. You likely will end up with at least 10 SPs for inserting data, 10 SPs for modifying data, 10 SPs for deleting records, and every SP will have almost as many in-parameters as there are fields in the table. OK, shall we do some code generation then?

How do I manage the amount of time and work required to write stored procedures from scratch?

Use XSLT to produce simple stored procedure boilerplates.

SP Design Model

Figure 1. Dog Registration Database

Most Web-based applications use the back-end DB in a quite straightforward way. Consider this use case: a user submits some data (name, address, phone number, etc.) that is processed by some business logic and then stored in the database. Later, someone asks for a report on the user’s details and the data is retrieved and presented. The user may come back and change the details or sign out, resulting in the deletion of the user records altogether.

Look at the sample Dog Registration Database shown in Figure 1. Get all DDL for the DB here.

The initial set of stored procedures for the tAnimalOfficer table may look like this:

CREATE PROCEDURE spNewtAnimalOfficer (@OfficerID nvarchar(255), @Name  nvarchar(255), @StationName  nvarchar(255), @TerritorialAuthorityID  int)ASINSERT INTO tAnimalOfficer (OfficerID, Name, StationName, TerritorialAuthorityID)VALUES (@OfficerID, @Name, @StationName, @TerritorialAuthorityID)GOCREATE PROCEDURE spUpdatetAnimalOfficer (@OfficerID  nvarchar(255), @Name  nvarchar(255), @StationName  nvarchar(255), @TerritorialAuthorityID  int)ASIF @Name IS NOT NULL UPDATE tAnimalOfficer SET [email protected] WHERE [email protected] @StationName IS NOT NULL UPDATE tAnimalOfficer SET [email protected] WHERE [email protected] @TerritorialAuthorityID IS NOT NULL UPDATE tAnimalOfficer SET [email protected] WHERE [email protected] PROCEDURE [dbo].[spDeletetAnimalOfficer] (@OfficerID  nvarchar(255))ASDELETE FROM tAnimalOfficer WHERE [email protected]

Your other SPs for this DB are not going to be much different because they all share the same underlying idea: all are plain insert, update, or delete statements.

SQL Server Metadata Views

The next step is to get the list of tables and their columns from SQL Server and then somehow generate similar SPs. SQL server has a number of views and tables for this. To cut a long story short, try this statement:

select * from sys.objects

Anything with type=’U’ is a user table. User tables that have is_ms_shipped=1 are in fact system tables that the server itself generates and those with is_ms_shipped=0 are the ones you created. However, I noticed that the is_ms_shipped flag is not always set correctly. The following is the SQL query to get a list of user tables for SP generation:

select * from sys.objects where type='U' and is_ms_shipped=0

The list of columns sits in the information_schema.columns view. Use this SQL statement to retrieve the basic information you need to generate the rest of the SPs:

select * from information_schema.columns where table_name='tAnimalOfficer' order by ordinal_position

.NET Code

All you need the .NET code to do is go into the database, get a list of tables, and then get a list of columns for every table and apply an XSLT file to generate those SP boilerplates. The trick is to get the data from SQL Server in XML. One of the ways to do that is to use the MS SQLXML component, which builds XML on the client side and returns an XmlReader object quickly and efficiently.

First of all, get a list of the tables in question with the following function:

  Private Shared Function GetTables(ByVal xCS As String) As XmlReader    Dim strCom As String = "select * from sys.objects where type='U' and is_ms_shipped=0 for xml raw"    Dim sqlCom As New SqlXmlCommand(xCS)    sqlCom.CommandText = strCom : sqlCom.ClientSideXml = True    sqlCom.CommandType = SqlXmlCommandType.Sql : sqlCom.RootTag = "tables"    Return sqlCom.ExecuteXmlReader  End Function

The function will return XML data that looks like this:

<tables>	<row name="tDog" object_id="117575457" schema_id="1"parent_object_id="0" type="U " type_desc="USER_TABLE" _
	create_date="2005-12-04T21:11:48.530" modify_date="2005-12-04T21:18:09.403" is_ms_shipped="0"  _
	is_published="0" is_schema_published="0"/>	<row name="tOwner" object_id="165575628" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" _
	create_date="2005-12-04T21:14:38.810" modify_date="2005-12-04T21:17:49.403" is_ms_shipped="0"  _
	is_published="0" is_schema_published="0"/>	<row name="tAnimalOfficer" object_id="277576027" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" _
	create_date="2005-12-04T21:18:09.310" modify_date="2005-12-04T21:18:09.403" is_ms_shipped="0"  _
	is_published="0" is_schema_published="0"/>	<row name="sysdiagrams" object_id="2089058478" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" _
	create_date="2005-12-04T21:06:54.200" modify_date="2005-12-04T21:06:54.207" is_ms_shipped="0"  _
	is_published="0" is_schema_published="0"/>tables>

You produce a list of columns in a similar way:

  Private Shared Function GetColumns(ByVal xCS As String, ByRef TableName As String) As XmlReader    Dim strCom As String = "select * from information_schema.columns where table_name='" + TableName + "' order by ordinal_position for xml raw"    Dim sqlCom As New SqlXmlCommand(xCS)    sqlCom.CommandText = strCom : sqlCom.ClientSideXml = True    sqlCom.CommandType = SqlXmlCommandType.Sql : sqlCom.RootTag = "columns"    Return sqlCom.ExecuteXmlReader  End Function

The function returns the following:

<columns>	<row TABLE_CATALOG="DevX" TABLE_SCHEMA="dbo" TABLE_NAME="tAnimalOfficer" COLUMN_NAME="OfficerID" ORDINAL_POSITION="1"  _
	IS_NULLABLE="NO" DATA_TYPE="nvarchar" CHARACTER_MAXIMUM_LENGTH="255" CHARACTER_OCTET_LENGTH="510" CHARACTER_SET_NAME="UNICODE"  _
	COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS"/>	<row TABLE_CATALOG="DevX" TABLE_SCHEMA="dbo" TABLE_NAME="tAnimalOfficer" COLUMN_NAME="Name" ORDINAL_POSITION="2"  _
	IS_NULLABLE="YES" DATA_TYPE="nvarchar" CHARACTER_MAXIMUM_LENGTH="255" CHARACTER_OCTET_LENGTH="510" CHARACTER_SET_NAME="UNICODE"  _
	COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS"/>	<row TABLE_CATALOG="DevX" TABLE_SCHEMA="dbo" TABLE_NAME="tAnimalOfficer" COLUMN_NAME="StationName" ORDINAL_POSITION="3"  _
	IS_NULLABLE="YES" DATA_TYPE="nvarchar" CHARACTER_MAXIMUM_LENGTH="255" CHARACTER_OCTET_LENGTH="510" CHARACTER_SET_NAME="UNICODE"  _
	COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS"/>	<row TABLE_CATALOG="DevX" TABLE_SCHEMA="dbo" TABLE_NAME="tAnimalOfficer" COLUMN_NAME="TerritorialAuthorityID" ORDINAL_POSITION="4"  _
	IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0"/>columns>

Glue these two functions together with this code:

  Public Shared Function GenratedSPs(ByRef XsltFileName As String, ByRef DbConnectionString As String) As String    Dim oStringWriter As New IO.StringWriter    oStringWriter.NewLine = vbCrLf     Dim xslDoc As New Xml.Xsl.XslTransform     Dim xslResolver As New Xml.XmlUrlResolver     xslDoc.Load(XsltFileName)     Dim xTables As Xml.XmlReader = GetTables(DbConnectionString)    Do While xTables.Read      If xTables.LocalName = "row" Then         Dim sTableName As String = xTables.GetAttribute("name")         Dim xColumns As Xml.XmlReader = GetColumns(DbConnectionString, sTableName) '        Dim xC As New Xml.XPath.XPathDocument(xColumns)         Dim stOut As New IO.StringWriter         xslDoc.Transform(xC, Nothing, stOut, xslResolver)         oStringWriter.Write(stOut.ToString)         oStringWriter.WriteLine()       End If    Loop    oStringWriter.Flush()     Return oStringWriter.ToString   End Function 

The string returned by this last function contains the text for all the SPs it has generated. You will need to paste it in your MS SQL Management Studio Query Designer, check it, modify it where needed, and then run it. This is still a bit of manual work, but just think how much time it would take to type all those parameters and declarations from scratch.

The sample XSLT file included with the full source code download for this article is designed to deal with the XML formats shown above and produce simple SP boilerplates as described in the SP Design Model section.

Customization

The .NET code is unlikely to need any customization?it does what it does. The actual SP text is in the XSLT. So, if you prefer a different SP boilerplate, you need to look into the XSLT file.

Limitations

Keep in mind that this is only a 10-Minute Solution (even if I did spend half a day getting it all going), so has its limitations:

• Not all SQL data types are used by the XSLT.
• The very first field in the table is considered to be the Primary Key.
• No composite keys are considered.
• No special constraints are considered (e.g., usually you cannot do an insert into an identity column).