This post was already published yesterday, someone deleted it by mistake so I had to recreate it…sorry for that (and thanks for google cache :-))

SSMS Tools PACK is an Add-In (Add-On) for Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express, this tool is developed by Mladen Prajdić.

SSMS Tools PACK contains a few upgrades to the IDE that were missing from Management Studio:

Some of these features are now available in SSMS 2008 but the tool is still very useful if you are using SSMS 2008. I am going to focus on the CRUD (Create, Read, Update, Delete) stored procedure generation functionality.

SSMS Tools PACK is available for the following SSMS versions

SQL Server Management Studio 2008

SQL Server Management Studio 2008 Express

SQL Server Management Studio 2005

SQL Server Management Studio 2005 Express

Download SSMS Tools PACK here: http://www.ssmstoolspack.com/Download.aspx

After it is installed create this table

CREATE TABLE Test (ID INT PRIMARY KEY IDENTITY,
                    LastName VARCHAR(40) not null,
                    FirstName VARCHAR(40) not null,
                    MiddleInitial CHAR(1)  null,
                    Salutation VARCHAR(10) null,
                    InsertedDate DATETIME not null,
                    LastUpdatedDate DATETIME not null)

Now from the menu select SSMS Tools–>CRUD Generator–>Options (see pic below)

I like to prefix my procs with pr instead of usp_ (See pic below)

You can customize how the procs will be generated, so for example the INSERT proc template looks like this

IF OBJECT_ID('[|schema|].[|procName|]') IS NOT NULL
BEGIN
    DROP PROC [|schema|].[|procName|]
END
GO
 
/*
Created by: SQLDenis
Version:    1.0
 
 
 
*/
CREATE PROC [|schema|].[|procName|]
|inputParams|
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON  
   
    BEGIN TRAN
   
    INSERT INTO [|schema|].[|tableName|] (|insertColumnList|)
    SELECT |values|
   
    -- Begin Return Select <- do not remove
    SELECT |selectColumnList|
    FROM   [|schema|].[|tableName|]
    WHERE  |whereStatement|
    -- End Return Select <- do not remove
               
    COMMIT
GO

As you can see I added a comment block there that has my name and the initial version number. Having all your developers use a tool like this is great because you will have all the same looking procs and don’t have to worry that some people use @Error or @ErrorCode or @ErrorID. You just modify the template to have the error checking that you have standardized upon and you are done

So now let’s see what SSMS Tools PACK generates

Right click on the table you created, select SSMS Tools and then Create CRUD (See image below)

The following code will be generated

USE [Test];
GO
 
IF OBJECT_ID('[dbo].[prTestSelect]') IS NOT NULL
BEGIN
    DROP PROC [dbo].[prTestSelect]
END
GO
 
/*
Created by: SQLDenis
Version:    1.0
 
 
 
*/
CREATE PROC [dbo].[prTestSelect]
    @ID INT
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON  
 
    BEGIN TRAN
 
    SELECT [ID], [FirstName], [InsertedDate], [LastName], [LastUpdatedDate], [MiddleInitial], [Salutation]
    FROM   [dbo].[Test]
    WHERE  ([ID] = @ID OR @ID IS NULL)
 
    COMMIT
GO
IF OBJECT_ID('[dbo].[prTestInsert]') IS NOT NULL
BEGIN
    DROP PROC [dbo].[prTestInsert]
END
GO
 
/*
Created by: SQLDenis
Version:    1.0
 
 
 
*/
CREATE PROC [dbo].[prTestInsert]
    @FirstName VARCHAR(40),
    @InsertedDate DATETIME,
    @LastName VARCHAR(40),
    @LastUpdatedDate DATETIME,
    @MiddleInitial CHAR(1),
    @Salutation VARCHAR(10)
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON  
   
    BEGIN TRAN
   
    INSERT INTO [dbo].[Test] ([FirstName], [InsertedDate], [LastName], [LastUpdatedDate], [MiddleInitial], [Salutation])
    SELECT @FirstName, @InsertedDate, @LastName, @LastUpdatedDate, @MiddleInitial, @Salutation
   
    -- Begin Return Select <- do not remove
    SELECT [ID], [FirstName], [InsertedDate], [LastName], [LastUpdatedDate], [MiddleInitial], [Salutation]
    FROM   [dbo].[Test]
    WHERE  [ID] = SCOPE_IDENTITY()
    -- End Return Select <- do not remove
               
    COMMIT
GO
IF OBJECT_ID('[dbo].[prTestUpdate]') IS NOT NULL
BEGIN
    DROP PROC [dbo].[prTestUpdate]
END
GO
 
/*
Created by: SQLDenis
Version:    1.0
 
 
 
*/
CREATE PROC [dbo].[prTestUpdate]
    @ID INT,
    @FirstName VARCHAR(40),
    @InsertedDate DATETIME,
    @LastName VARCHAR(40),
    @LastUpdatedDate DATETIME,
    @MiddleInitial CHAR(1),
    @Salutation VARCHAR(10)
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON  
   
    BEGIN TRAN
 
    UPDATE [dbo].[Test]
    SET    [FirstName] = @FirstName, [InsertedDate] = @InsertedDate, [LastName] = @LastName, [LastUpdatedDate] = @LastUpdatedDate, [MiddleInitial] = @MiddleInitial, [Salutation] = @Salutation
    WHERE  [ID] = @ID
   
    -- Begin Return Select <- do not remove
    SELECT [ID], [FirstName], [InsertedDate], [LastName], [LastUpdatedDate], [MiddleInitial], [Salutation]
    FROM   [dbo].[Test]
    WHERE  [ID] = @ID   
    -- End Return Select <- do not remove
 
    COMMIT TRAN
GO
IF OBJECT_ID('[dbo].[prTestDelete]') IS NOT NULL
BEGIN
    DROP PROC [dbo].[prTestDelete]
END
GO
 
/*
Created by: SQLDenis
Version:    1.0
 
 
 
*/
CREATE PROC [dbo].[prTestDelete]
    @ID INT
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON  
   
    BEGIN TRAN
 
    DELETE
    FROM   [dbo].[Test]
    WHERE  [ID] = @ID
 
    COMMIT
GO
 
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

As you can see that is a huge time saver, you can of course customize it so that it conforms to your style guide.

Don’t forget to thank Mladen Prajdić on twitter: http://twitter.com/MladenPrajdic or to donate if this tool is useful to you

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum