I learned something new today. In SQL Server you can create temporary stored procedures that automatically go away when your session closes.

Here is a good article on Temporary Stored Procedures.

I already knew that you could create temporary tables, but I hadn't known that the same applied to stored procedures.

Temporary tables are created by starting the table name with a # sign.

Example:

create table #mytbl 

( 

    [Name] varchar(50), 

    [Age] int 

)

The same trick is used for stored procedures.

Example:

create procedure #addName 

( 

    @Name varchar(50),

    @Age int 

) 

begin 

    insert into #myTbl([Name], [Age]) values(@Name, @Age) 

end

This is really useful for scripts that you use for different tasks that contain repeated code that you hate duplicating but you don’t really want to permanently load into the database.

For example, say you have a script that runs on your build and deployment that fixes up any server logins that may have become broken from a rebuild or some other action. You might have a script that looks like the following.

declare @name varchar(100)

set @name = 'NT AUTHORITY\NETWORK SERVICE'

-- check to see if login exists

if exists(select name from master.sys.syslogins where name = @name)

begin

    -- if login exists, check to see if user exists on db

    if not exists(select name from sys.sysusers where name = @name)

    begin

        -- if user does not exist on db, then add it and add roles

        exec sp_grantdbaccess @name

        exec sp_addrolemember 'db_owner', @name

        print @name

    end

    else

    begin

        exec SP_CHANGE_USERS_LOGIN 'AUTO_FIX', @name, NULL, '*****'

    end

end

set @name = 'APPSVR\svnprojects'

-- check to see if login exists

if exists(select name from master.sys.syslogins where name = @name)

begin

    -- if login exists, check to see if user exists on db

    if not exists(select name from sys.sysusers where name = @name)

    begin

        -- if user does not exist on db, then add it and add roles

        exec sp_grantdbaccess @name

        exec sp_addrolemember 'db_owner', @name

        print @name

    end

    else

    begin

        exec SP_CHANGE_USERS_LOGIN 'AUTO_FIX', @name, NULL, '*****'

    end

end

set @name = 'APPSRV\agentmgr'

-- check to see if login exists

if exists(select name from master.sys.syslogins where name = @name)

begin

    -- if login exists, check to see if user exists on db

    if not exists(select name from sys.sysusers where name = @name)

    begin

        -- if user does not exist on db, then add it and add roles

        exec sp_grantdbaccess @name

        exec sp_addrolemember 'db_owner', @name

        print @name

    end

    else

    begin

        exec SP_CHANGE_USERS_LOGIN 'AUTO_FIX', @name, NULL, '*****'

    end

end

This contains lots of duplicate code which is more difficult to read is also error prone if you need to update the code that is performed for each user.

Now consider the updated code using a temp stored procedure.

create procedure #addLogin

(

    @name varchar(256),

    @role varchar(256),

    @password varchar(256)

)

as

begin

    -- check to see if login exists

    if exists(select name from master.sys.syslogins where name = @name)

    begin

        -- if login exists, check to see if user exists on db

        if not exists(select name from sys.sysusers where name = @name)

        begin

            -- if user does not exist on db, then add it and add roles

            exec sp_grantdbaccess @name

            exec sp_addrolemember @role, @name

            print @name + ' granted access'

        end

        else

        begin

            exec SP_CHANGE_USERS_LOGIN 'AUTO_FIX', @name, NULL, @password

            print @name + ' fixed.'

        end

    end

end

go

exec #addLogin @name = 'NT AUTHORITY\NETWORK SERVICE', @role='db_owner', @password='*****'

exec #addLogin @name = 'APPSVR\svnprojects', @role='db_owner', @password='*****'

exec #addLogin @name = 'APPSRV\agentmgr', @role='db_owner', @password='*****'

go

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.

svnbridge keeps crashing so I wasn't able to updated the code on Codeplex Source Code section hasn't been updated, but I posted a new release which is the templates in source code form.

As chance would have it, MyGeneration released v1.3 the same day I published this project. The scripts "Data Access Layer (C#, SQL Server) v1.0" and "Data Access Layer (C#, SQL Server) v2.0" were written to take into account certain bugs in MyGeneration v1.2. Those bugs have been fixed in MyGeneration v1.3 so if you are seeing weird results like SQL Server datatypes being used as C# data types then this is why.

An update will be released soon that fixes this problem and I will post an update here and on codeplex when this issue has been resolved.

All code published in this article is published under the Microsoft Public License. See source code download for a copy of the license.

Copyright 2007 Zack Moore

Code can be downloaded from here.

Intro

Data Access Layers are one of those problems that people keep coming up with solutions for. There are many reasons for this, but basically it boils down to two things. People are different and different people solve problems differently. Data is different and people need to access different kinds of data differently.

I wanted to build something that was simple, easy to use, easy to extend, and compatible with data binding. I wanted to be able to automatically generate well written stored procedures for all the standard CRUD (Create Read Update Delete) and also allow me to add my own custom written stored procedures. I wanted to be able to call all of my stored procedures from .NET code without having to manually write all of the ADO.NET and I wanted the compiler to check my method names and my parameters like a regular .NET method. I wanted to be able to use data binding with objects instead of DataSets. And last, the data binding had to be two way and not just for display.

Code Generation

Code Generation is the act of having one computer program write another computer program and it is a great way to build components that repeat a pattern like stored procedures and ADO.NET code.

There are different ways of performing code generation. One way is text output. A computer program can easily spit out a file containing C# or VB.NET or any language you want. That is how MyGeneration and CodeSmith work or you could write a program yourself that builds a code file. In .NET you could also use the Code DOM to write code using a structured framework.

I use MyGeneration: http://www.mygenerationsoftware.com/

MyGeneration is a free open source code code generation tool. Code Generation scripts are writing like classic ASP. You use a set of tags to escape in and out of script text and script code. Usually these are the same tags as used in classic ASP <% and %> but they are configurable. My Generation supports writing scripts in VB Script, JScript, C#, and VB.NET. In addition to the code generation code, each script also supports a user interface section where you can prompt the user for code generation parameters. MyGeneration also supports saving the script parameters in a file so that you can run the script over and over without having to go through the UI each time so long as you don't need to change the parameters.

Stored Procedures

The first step in building a stored procedure based DAL is to generate your stored procedures. I started with an excellent script titled "Script Insert/Update/Delete Procedures for SQL Server" written by Justin Greenwood. Justin wrote his script in February of 2004 for SQL Server 2000  and I wanted my script to work with SQL Server 2005 so I needed to make some changes. In addition, I had some additional features that I wanted to add.

I would like to extend my thanks to Justin for writing his very useful script and for giving me permission to redistribute my modified version.

Justin's original script included a lot of features like its handling of primary keys in where clauses, treatment of computed and identity columns, and its use of timestamp columns in update procedures.

I made a lot of changes, so I am sure that any mistakes are more likely mine.

Lets take a look at how the script handles a simple sample database.

One point I would like to make as we begin looking at generated code is that none of the generated code has a date emended in it. Its common for programmers to want to put a generated on date in code that is output so that they can tell when it was produced, but there is a very good reason not to. If you use source control, every time you check in a file your source control system checks to see if the file changed. If not, then it doesn't do anything, but if it did then it creates a new revision and checks in your changes. Now if you add a generated on date to your output, then every time you regenerate you create a new generated on date even if the actual code that was output didn't change at all. This means that your source control system has to create a new revision and it becomes harder for you to track real changes when you are having to sort through a bunch of revisions where only the dates changed. Let your source control system track when things changed. Leave dates and other unnecessary artifacts out of your generated code.

To begin, run the script "Basic CRUD Plus SelectBy FK". This script is under the MyGeneration namespace "ZacksFiasco.DataAccessLayer.SQLServer2005". You should see a UI screen that looks like the following:

The first parameter is the path on your computer to write the stored procedure files to. For testing, you could leave it to the default. However, if you use this for a project then I suggest you point it to a folder in your project where you would like to store your stored procedure scripts.

The second parameter is the SQL Server schema to store your procedures under. The UI defaults this to 'api'. You could put your procedures under 'dbo' but I suggest that you don't. Lots of things can put procedures into 'dbo'. In a later part of this series we will look at another code generation script that will take our stored procedures and build a .NET DAL and it will use the schema to filter only the stored procedures that we want. (Note: if you use 'api' or another schema that doesn't exist, the be sure to create that schema in your database before trying to load your stored procedures.)

The next parameter is the database to read.

The last parameter is the set of tables to generate procedures for. One file will be generated for each table, so for our test two files while be generated: sql_procs_Table1.sql and sql_procs_Table2.sql.

Generated TSQL

This example generates 381 lines of TSQL so I won't be able to go over the entire output. Instead I will try to give a quick synopsis of the key points.

The insert statements are pretty basic.

-- 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

The stored procedure does not use newid() to generate the key. My personal preference is to generate the key in the application. There are many times when it is beneficial to know the key in the application before inserting the record. In this example we are using a uniqueidentifier which in .NET is a System.Guid but if we were using an int there are other ways of generating a unique value.

If you are concerned about SQL Server clustering of uniqueidentifiers I suggest that you use custom code to generate your Guids.

If this table did use a auto-key, this script is smart enough return the generated value as an OUTPUT parameter.

Also note that the rowversion column is returned as an OUTPUT parameter.

Take a look at the last line of the comment section. It is an XML block. For now I will defer the discussion of this, but we will use this in our DAL code.

The update procedure attempts to update the row in the database. Rowversion is an OUTPUT parameter which is in/out. First rowversion is used for optimistic concurrency. If the update succeeds, the the new rowversion is returned in the same parameter. If the update fails then an error is raised.

-- 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

The delete procedure is pretty simple. It just deletes by the primary key.

-- Proc: Table1Delete
-- 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="Delete"/>
CREATE PROCEDURE [api].[Table1Delete]
(
    @Table1Id uniqueidentifier
)
AS
BEGIN
    SET NOCOUNT ON
    DELETE
     FROM [dbo].[Table1]
    WHERE
          [Table1Id] = @Table1Id

    RETURN @@Error
END
GO

The select procedure is also pretty basic. There is also a SelectAll procedure which is identical except it doesn't have a where clause.

-- Proc: Table1Select
-- 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="Select"><!--
--><Keys><!--
--><Key>Table1Id</Key><!--
--></Keys><!--
--></MetaData>
CREATE PROCEDURE [api].[Table1Select]
(
    @Table1Id uniqueidentifier
)
AS
BEGIN
    SET NOCOUNT ON
    select
        [Table1Id],
        [Col1],
        [Col2],
        [rowversion]
    FROM [dbo].[Table1]
    WHERE 
         [Table1Id] = @Table1Id

    RETURN @@Error
END
GO

One of the cool enhancements of my updated script is that it also generates SelectBy<ForeignKey> procedures. In this example we generated a procedure that selects records from Table2 where it matches the foreign key from Table1.

-- Proc: Table2SelectByTable1
-- 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="Table2" commandType="Select"><!--
--><Keys><!--
--><Key>Table2Id</Key><!--
--></Keys><!--
--></MetaData>
CREATE PROCEDURE [api].[Table2SelectByTable1]
(
    @Table1Id uniqueidentifier
)
AS
BEGIN
    SET NOCOUNT ON
    select
        [Table2Id],
        [Col1],
        [Table1Id],
        [UpdatedOn],
        [UpdatedBy]
    FROM [dbo].[Table2]
    WHERE
          [Table1Id] = @Table1Id

    RETURN @@Error
END
GO

This script looks for certain column names that it treats differently. If a column is named 'UpdatedOn' then the script generates code to mark this parameter as an OUTPUT parameter in insert and update procedures and assigns the value from getdate(). A future enhancement might make these columns configurable instead of hard-coding the column names in the script.

-- Proc: Table2Insert
-- 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="Table2" commandType="Insert"/>
CREATE PROCEDURE [api].[Table2Insert]
(
    @Table2Id uniqueidentifier,
    @Col1 varchar(50) = NULL,
    @Table1Id uniqueidentifier,
    @UpdatedOn datetime OUTPUT,
    @UpdatedBy uniqueidentifier = NULL
)
AS
BEGIN
    SET NOCOUNT ON
    
    set @UpdatedOn = getdate();
    
    INSERT INTO [dbo].[Table2]
    (
        [Table2Id],
        [Col1],
        [Table1Id],
        [UpdatedOn],
        [UpdatedBy]
    )
    VALUES
    (
        @Table2Id,
        @Col1,
        @Table1Id,
        @UpdatedOn,
        @UpdatedBy
    )

    RETURN @@Error

END
GO

-- Proc: Table2Update
-- 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="Table2" commandType="Update"/>
CREATE PROCEDURE [api].[Table2Update]
(
    @Table2Id uniqueidentifier,
    @Col1 varchar(50) = NULL,
    @Table1Id uniqueidentifier,
    @UpdatedOn datetime OUTPUT,
    @UpdatedBy uniqueidentifier = NULL
)
AS
BEGIN
    SET NOCOUNT ON
    
    set @UpdatedOn = getdate();
    
    UPDATE [dbo].[Table2]
    SET
        [Table2Id] = @Table2Id,
        [Col1] = @Col1,
        [Table1Id] = @Table1Id,
        [UpdatedOn] = @UpdatedOn,
        [UpdatedBy] = @UpdatedBy
    WHERE
         [Table2Id] = @Table2Id

    RETURN @@Error
END
GO

I've talked mostly in this article about what the code generation script outputs and not how it generates code. The key to generating a good code generation script is to first decide what it should output for each scenario. The second key is a good meta-data engine. MyGeneration has an excellent meta-data engine and this is how I can easily gather information on the tables and their keys.

Download the source code and take a look at how the scripts work.

This concludes Part 1. We have seen how you can generate basic CRUD stored procedures for our database. The script generates good TSQL and includes features for rowversion/timestamp columns, UpdatedOn columns, identities, keys, and foreign keys.

Part 2 will talk about taking these stored procedures, writing custom stored procedures and generating a basic Data Access Layer where we can call each of these stored procedures from .NET applications.