All code published in this article is published under the Microsoft Public License. See source code download for a copy of the license.
Copyright 2008 Zack Moore
Code can be downloaded from here.
During the course of writing the next part in this series, I made some changes to the scripts. I fixed some bugs in how computed columns were handled, cleaned up some inefficient code, added a lot more comments, and I changed the Insert and Update procedures to make better use of the SQL Server 2005 OUTPUT clause in returning computed and identity columns. This leads into a good discussion of the structure of MyGeneration scripts, how to change them, and how to version them.
Before I begin discussion of the code there are some things you need to know about MyGeneration scripts. Each script is identified by a Unique ID which is a GUID. If you edit a script that you did not create, then it is a good idea to create a new id for that script. Otherwise it could be overwritten or confused with the original script. It is also a good idea to update the title to reflect that it is a modified version of the original.
This also requires you to consider what to do when updating your own script. What do you do if you make a change or fix a bug in your own script? Should you create a new id or keep the old one? One option would be to create a new id every time you edit a script, but that is tedious and would also clutter the namespace with many versions of the same script.
For my scripts, if the change is small and does not cause a breaking change in the interface then I keep the same id and I add a version to the Title and update the minor version number. If the change is major, then I create a new id and update the Title of the script to include a version number and update the major or minor version number.
In this example, I'm going to modify the SQL that the stored procedures produce, so I'm only going to update the minor version number and keep the same id.
As you can see from the script properties, the CRUD script is written in JScript. This script has the following basic format.
var fkProcList = new Array();
for (var i = 0; i < tablenames.Count; i++)
{
// Loop through all the columns of the table
for (var j = 0; j < tableMeta.Columns.Count; j++)
{
}
%>
Stored procedures
<%
for(var x = 0; x < tableMeta.ForeignKeys.Count; x++)
{
%>
SelectByFK procedures
<%
}
// Save this set of procedures to disk
output.save(filename, false);
buffer += output.text;
output.clear();
}
output.write(buffer);
There is a lot left out of this skeleton, but essentially the script loops through each table and processes each column. The script then creates the Select, Select All, Insert, Update, and Delete procedures. Then the script examines the foreign keys and creates the SelectBy Foreign key procedures. Then all of the TSQL is written to a file and the process starts over with the next table.
The CRUD script v1.0 produces the following TSQL for INSERT and UPDATE.
-- Proc: Table1Insert
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Insert"/>
CREATE PROCEDURE [api].[Table1Insert]
(
@Table1Id uniqueidentifier,
@Col1 varchar(50),
@Col2 int = NULL,
@rowversion timestamp OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[Table1]
(
[Table1Id],
[Col1],
[Col2]
)
VALUES
(
@Table1Id,
@Col1,
@Col2
)
SELECT @rowversion = [rowversion]
FROM [dbo].[Table1]
WHERE [Table1Id] = @Table1Id;
RETURN @@Error
END
GO
-- Proc: Table1Update
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Update"/>
CREATE PROCEDURE [api].[Table1Update]
(
@Table1Id uniqueidentifier,
@Col1 varchar(50),
@Col2 int = NULL,
@rowversion timestamp OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @t TABLE(x int);
UPDATE [dbo].[Table1]
SET
[Table1Id] = @Table1Id,
[Col1] = @Col1,
[Col2] = @Col2
OUTPUT 1 into @t(x)
WHERE
[Table1Id] = @Table1Id AND
[rowversion] = @rowversion
IF(SELECT COUNT(*) FROM @t) = 0
BEGIN
RAISERROR('Concurrency Error',16,1)
END
SELECT @rowversion = [rowversion]
FROM [dbo].[Table1]
WHERE [Table1Id] = @Table1Id;
RETURN @@Error
END
GO
These procedures add and update records to the table and then run a second query to retrieve the rowversion which is returned as a OUTPUT parameter. The purpose of this is to allow an application to continue to use the data they have and if necessary perform an update. If the rowversion wasn't returned, then the application would have to query the entire record again in order to perform an update.
I would like to modify the code generation script so that it produces INSERT procedures that don't need to run a separate query to return the rowversion.
Using a text editor, I edited the above TSQL until it looked how I think I want it. After testing the new procedures, we end up with the following:
-- Proc: Table1Insert
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Insert"/>
CREATE PROCEDURE [api].[Table1Insert]
(
@Table1Id uniqueidentifier,
@Col1 varchar(50),
@Col2 int = NULL,
@rowversion timestamp = NULL OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @t TABLE
(
[rowversion] binary(8)
);
INSERT INTO [dbo].[Table1]
(
[Table1Id],
[Col1],
[Col2]
)
OUTPUT
rowversion
INTO @t
(
[rowversion]
)
VALUES
(
@Table1Id,
@Col1,
@Col2
)
SELECT
@rowversion = [rowversion]
FROM @t
RETURN @@Error
END
GO
-- Proc: Table1Update
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Update"/>
CREATE PROCEDURE [api].[Table1Update]
(
@Table1Id uniqueidentifier,
@Col1 varchar(50),
@Col2 int = NULL,
@rowversion timestamp OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @t TABLE
(
[rowversion] binary(8)
);
UPDATE [dbo].[Table1]
SET
[Table1Id] = @Table1Id,
[Col1] = @Col1,
[Col2] = @Col2
OUTPUT
[rowversion]
into @t
(
[rowversion]
)
WHERE
[Table1Id] = @Table1Id AND
[rowversion] = @rowversion
IF(SELECT COUNT(*) FROM @t) = 0
BEGIN
RAISERROR('Concurrency Error',16,1)
END
SELECT
@rowversion = [rowversion]
FROM @t
WHERE
[Table1Id] = @Table1Id;
RETURN @@Error
END
GO
The updated procedures use the new OUTPUT clause to return the rowversion to a table variable. We can then query the table variable for the rowversion and return it in the OUTPUT parameter. The query against the table variable should be much faster than a query against the main table since the table variable has fewer records and is in memory.
In order to produce this new TSQL we need to modify our code generation script.
When the script loops over each column, it builds the pieces it needs to build the the procedures. The variable insertParams contains the string of comma seperated parameters of the Insert procedure. The variable insertFields is the list of columns to be inserted by the insert statement. The variable insertValues maps the insert parameters to the columns.
The section of code that produces the INSERT procedure looks like the following:
-- Proc: <%= insertProcName %>
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="<%=tablename%>" commandType="Insert"/>
CREATE PROCEDURE [<%=mySchema%>].[<%= insertProcName %>]
(
<%= insertParams %>
)
AS
BEGIN
SET NOCOUNT ON
<% if(updatedOn == true) { %>
set @UpdatedOn = getdate();
<%}%>
INSERT INTO <%= tablenameFull %>
(
<%= insertFields %>
)
VALUES
(
<%= insertValues %>
)
<%= (insertAutoKeyCode == "" ? "" : "\r\n" + insertAutoKeyCode) %><%
if (hasComputedFields)
{
insertComputedCode += "\r\n\tFROM " + tablenameFull + "\r\n";
insertComputedCode += "\tWHERE " + deleteWhere + ";\r\n";
}
%>
<%=insertComputedCode%>
RETURN @@Error
END
GO
The deleteWhere variable may stand out as being out of place. This variable contains the code for the delete WHERE clause, but some of the other procedures require exactly the same code so it gets reused in several places.
One of the things to notice is the computed fields section. In the current script, this is where the timestamp field value is retrieved since technically a timestamp is computed automatically. So when we change the code that returns the timestamp columns, this also needs to work for other computed columns.
In order to retrieve the computed columns we need to create our table variable, specify our OUTPUT clause, copy the values from the table variable into the OUTPUT parameters. This is accomplished by using these three variables.
var insertComputedTableVar = ""; // generated TSQL to hold computed values
var insertComputedCode = ""; // generated TSQL to retrieve Computed values
var insertComputedReturn = ""; // code to return values into output parameters
You can see the first variable used in the script above, but we are going to modify the values it holds. The second two variables are new. In addition, we are getting rid of insertAutoKeyCode. AutoKeys are what MyGeneration calls like identity columns and in our modification they will be handled by the computed column code.
The code below is executed inside a loop over each table's columns and populates our three variables. It uses a simple pattern to append a comma and new line if there is more than one computed value.
// generate code to retrieve computed values on insert
if (column.IsComputed || column.IsAutoKey)
{
hasComputedFields = true;
// build the list of values to go in the
// OUTPUT clause
if (insertComputedCode != "")
{
insertComputedCode += ", \r\n";
}
insertComputedCode += "\t\tINSERTED.[" + column.Name + "]";
// build the list of columns for the table variable
// to hold the OUTPUT values
if(insertComputedTableVar != "")
{
insertComputedTableVar += ", \r\n";
}
if(column.DataTypeName == "timestamp")
{
insertComputedTableVar += "\t\t[" + column.Name + "] binary(8)";
}
else
{
insertComputedTableVar += "\t\t[" + column.Name + "] " + column.DataTypeNameComplete;
}
// build a list of select columns to return
// output parameters
if(insertComputedReturn != "")
{
insertComputedReturn += ", \r\n";
}
insertComputedReturn += "\t\t@" + paramName + " = [" + column.Name + "]";
}
Take note that this code contains a special case for timestamp in the table variable code that it generates. The table variable is used to store values that get spit out by the insert statement when we use an OUTPUT clause. So we declare a column in the table variable for each value we want to output and we create each column in the temp table to be the same data type as they are in the real table. The only problem is if we declare a timestamp column on our temp table, it wants to behave the way timestamp columns always behave and it will forbid you form trying to insert a value into it. The solution is to use a binary(8) column to store the timestamp and all is well.
With this new code we can update our procedure generation script to the following.
-- Proc: <%= insertProcName %>
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="<%=tablename%>" commandType="Insert"/>
CREATE PROCEDURE [<%=mySchema%>].[<%= insertProcName %>]
(
<%= insertParams %>
)
AS
BEGIN
SET NOCOUNT ON
<% if(hasComputedFields) { %>
declare @t table
(
<%=insertComputedTableVar%>
)
<%
}
if(updatedOn == true) { %>
set @UpdatedOn = getdate();
<%
}%>
INSERT INTO <%= tablenameFull %>
(
<%= insertFields %>
)<%
if(hasComputedFields) { %>
OUTPUT
<%=insertComputedCode%>
INTO @t<%
}%>
VALUES
(
<%= insertValues %>
)
<%if(hasComputedFields) { %>
SELECT
<%=insertComputedReturn%>
FROM @t
<%}%>
RETURN @@Error
END
GO
The same process is repeated for update and then all we have to do is regenerate our stored procedures.
The final TSQL looks like this.
-- Proc: Table1Insert
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Insert"/>
CREATE PROCEDURE [api].[Table1Insert]
(
@Table1Id uniqueidentifier,
@Col1 varchar(50),
@Col2 int = NULL,
@rowversion timestamp = NULL OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
declare @t table
(
[rowversion] binary(8)
)
INSERT INTO [dbo].[Table1]
(
[Table1Id],
[Col1],
[Col2]
)
OUTPUT
INSERTED.[rowversion]
INTO @t
VALUES
(
@Table1Id,
@Col1,
@Col2
)
SELECT
@rowversion = [rowversion]
FROM @t
RETURN @@Error
END
GO
-- Proc: Table1Update
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Update"/>
CREATE PROCEDURE [api].[Table1Update]
(
@Table1Id uniqueidentifier,
@Col1 varchar(50),
@Col2 int,
@rowversion timestamp = NULL OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
declare @t table
(
[rowversion] binary(8)
)
UPDATE [dbo].[Table1]
SET
[Table1Id] = @Table1Id,
[Col1] = @Col1,
[Col2] = @Col2
OUTPUT
INSERTED.[rowversion]
INTO @t
WHERE
[Table1Id] = @Table1Id AND
[rowversion] = @rowversion
IF(SELECT COUNT(*) FROM @t) = 0
BEGIN
RAISERROR('Concurrency Error',16,1)
END
SELECT
@rowversion = [rowversion]
FROM @t
RETURN @@Error
END
GO
The updated code generation scripts are posted as Release 1.2 on CodePlex.
Give this a try in your own databases.
