Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATED

By Chris Morton,

First execute 'script 1'

Then execute script 2'


Once the stored procedure _SPLOG_SPConstructor has been made execute it with the default parameters.
These stored procedures generate common and very simple stored procedures. There is commented text in the 2nd stored procedure that allows u to observe usage statistics that can be used to remove the unused stored procedures once development is finished. For example you would remove all stored procedures with a usage of 0.
This code has been tested on smallish databases with a reasonable number of columns. it works and saves quite a lot of time.
there are a few known bugs:
If your table names do not conform to normal naming conventions (i.e. have spaces in the table names) you might have difficulty.
Also the code that is generated has to fit into 8000 characters.
Also it does not support timestamp datatypes - and there could be others. it does support the common datatypes. Since its open source go ahead and change it. Just let me know please.
For all reasonable designs this will work as planned.

Use Testmode = 1 with CreateLog = 1 to generate the code but not to execute it.

Purpose:

The purpose of the script is to create all the 'logic' free sp's one is likely to use in a new application from a 'virgin' database.

It creates: selectbyid, selectall, insert, update stored procedures for every table in the database.

By virgin database I mean a normalized database with standard naming conventions (i.e. not spaces in table names or column names), with no stored procedures.

Installation:

Option 1: if you develop databases from scratch often I would recommend running this on the model database and then every database you create then on will include these two procs.

Option 2: run the create procs on an individual database.

Execution:

Once the sprocs are installed just run spconstructor, with default parameters or testmode = 1.

Advantage:

This saves a large amount of routine and 'boring' programming, as well as the time it typically takes to do it.

Disadvantage:

You may end up with redundant sprocs in you db. If you modify the code (uncomment) it allows you to keep a log of stored procedure usage during development, once the development is over just remove all the stored procedures with a usage of 0.

Limitations:

Depending on your coding style you may prefer to have you insert and updates in one proc. in this case you need to modify the open source.

Otherwise have fun!

These scripts use the metadata from you database to create 4 statements for each table, for example:

selectbyid, selectAll, update, insert

-- =============================================

-- AUTHOR: Chris Morton

-- CREATED DATE: 2008/10/23

-- AUTOGENERATED BY SQL CODE GENERATION TOOL VERSION 1.1 BETA - NO LIABILITY CAN BE CLAIMED FROM CHRIS MORTON - CONTACT cbmorton@gmail.com

-- =============================================

CREATE PROCEDURE voselectFeedbackCategoryByID
@FeedbackCategoryID BIGINT

--@FeedbackCategoryName varchar(50)
AS
BEGIN

SELECT [FeedbackCategoryID],
[FeedbackCategoryName]
FROM [FeedbackCategory]
WHERE FeedbackCategoryID = @FeedbackCategoryID

END
-- =============================================

-- AUTHOR: Chris Morton

-- CREATED DATE: 2008/10/23

-- AUTOGENERATED BY SQL CODE GENERATION TOOL VERSION 1.1 BETA - NO LIABILITY CAN BE CLAIMED FROM CHRIS MORTON - CONTACT cbmorton@gmail.com

-- =============================================

CREATE PROCEDURE voselectALLFeedbackCategory

--@FeedbackCategoryID bigint

--@FeedbackCategoryName varchar(50)
AS
BEGIN

SELECT [FeedbackCategoryID],
[FeedbackCategoryName]
FROM [FeedbackCategory]

END
-- =============================================

-- AUTHOR: Chris Morton

-- CREATED DATE: 2008/10/23

-- AUTOGENERATED BY SQL CODE GENERATION TOOL VERSION 1.1 BETA - NO LIABILITY CAN BE CLAIMED FROM CHRIS MORTON - CONTACT cbmorton@gmail.com

-- =============================================

CREATE PROCEDURE voinsertFeedbackCategory
@FeedbackCategoryID BIGINT,
@FeedbackCategoryName VARCHAR(50)
AS
BEGIN

INSERT INTO FeedbackCategory
(
[FeedbackCategoryID],
[FeedbackCategoryName]
)
VALUES (
@FeedbackCategoryID,
@FeedbackCategoryName
)

END
-- =============================================

-- AUTHOR: Chris Morton

-- CREATED DATE: 2008/10/23

-- AUTOGENERATED BY SQL CODE GENERATION TOOL VERSION 1.1 BETA - NO LIABILITY CAN BE CLAIMED FROM CHRIS MORTON - CONTACT cbmorton@gmail.com

-- =============================================

CREATE PROCEDURE voupdateFeedbackCategory
@FeedbackCategoryID BIGINT,
@FeedbackCategoryName VARCHAR(50)
AS
BEGIN

UPDATE FeedbackCategory
SET FeedbackCategoryName = @FeedbackCategoryName
WHERE FeedbackCategoryID = @FeedbackCategoryID

END

Total article views: 3030 | Views in the last 30 days: 7
 
Related Articles
FORUM

create Procedure within Procedure

create Procedure within Procedure

FORUM

Create procedure permission in schema

Create procedure permission in schema

FORUM

Create separate file for Procedure

Create separate file for Procedure

FORUM

How to create a view dynamically thru a procedure

Create a view dynamically thru a procedure[need to create view in another database]

FORUM

Create Database

Create database taking forever

Tags
autogenerate parameters    
chris morton    
codrakon    
insert    
metadata    
select    
stored procedures    
update    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones