Introduction

So you have added a number of new tables to your database and you now need to create stored procedures to insert, update, and delete from them. Now, depending on how many tables you have added,this can be a very time consuming task but fear not, as I will show you an automated solution that will build the procedures for you.

The code below will work for any table with a single primary key that is also an identity column. It could be extended to handle all different forms of tables but, in the time it would take you to get this working perfectly, you could have written the extra procedures manually! I personally try to automate as much of the repetitive work as I can, leaving me more time to spend on the good stuff, and I can bet the majority of you would like to do this as well.

You will also note that no select or "get" procedures are produced. Again, this is because these inevitably require more than one table and so cannot be automated easily.

So, let's dive in. We'll see three functions being used below; createInsertSP, createUpdateSP, and createDeleteSP (not the most inventive names, I know, but they do what they say on the tin!).

How it all works

All three functions share a similar structure and it would be too long-winded to go through all three, so we'll just concentrate on one so you can see how this works. All three functions return both the drop and create T-SQL strings for the procedure in question, separated by a pair of pipe characters.

The Create Insert Procedure Function

The logic in the create insert procedure function is as follows:

1. Creates a drop statement sql string for the procedure (in case you are re-running the process!)
2. Creates the create statement sql string like so:

• Create the parameter list (excluding the identity primary key but including defaults)
• Create a commented modifications section
• Begin a transaction
• Begin a try
• Create the insert statement and the return of the new identity (primary key) value
• Commit the transaction
• End the try
• Begin a catch
• Raise any error
• Roll back the transaction
• End the catch
• Return both the drop and the create strings separated by two pipe characters

Ok, so let's have a look at the code.

CREATE FUNCTION dbo.createInsertSP
(
 @spSchema varchar(200), -- desired schema
 @spTable varchar(200) -- desired table
)
RETURNS varchar(max)
AS
BEGIN
 declare @SQL_DROP varchar(max)
 declare @SQL varchar(max)
 declare @COLUMNS varchar(max)
 declare @PK_COLUMN varchar(200)
 set @SQL = ''
 set @SQL_DROP = ''
 set @COLUMNS = ''
 -- step 1: generate the drop statement and then the create statement
 set @SQL_DROP = @SQL_DROP + 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @spSchema + '].[insert' + @spTable + ']'') AND type in (N''P'', N''PC''))' + char(13)
 set @SQL_DROP = @SQL_DROP + 'DROP PROCEDURE [' + @spSchema + '].[insert' + @spTable + ']'
 set @SQL = @SQL + 'CREATE PROC [' + @spSchema + '].[insert' + @spTable + ']' + char(13)
 set @SQL = @SQL + '(' + char(13)
 -- step 2: ascertain what the primary key column for the table is
 set @PK_COLUMN = 
 (
 select c.column_name
 from information_schema.table_constraints pk 
 inner join information_schema.key_column_usage c 
 on c.table_name = pk.table_name 
 and c.constraint_name = pk.constraint_name
 where pk.TABLE_SCHEMA = @spSchema
 and pk.TABLE_NAME = @spTable
 and pk.constraint_type = 'primary key'
 and c.column_name in
 (
 select COLUMN_NAME
 from INFORMATION_SCHEMA.COLUMNS
 where columnproperty(object_id(quotename(@spSchema) + '.' + 
 quotename(@spTable)), COLUMN_NAME, 'IsIdentity') = 1 -- ensure the primary key is an identity column
 group by COLUMN_NAME
 )
 group by column_name
 having COUNT(column_name) = 1 -- ensure there is only one primary key
 )
 -- step 3: now put all the table columns in bar the primary key (as this is an insert and it is an identity column)
 select @COLUMNS = @COLUMNS + '@' + COLUMN_NAME 
 + ' as ' 
 + (case DATA_TYPE when 'numeric' then DATA_TYPE + '(' + convert(varchar(10), NUMERIC_PRECISION) + ',' + convert(varchar(10), NUMERIC_SCALE) + ')' else DATA_TYPE end)
 + (case when CHARACTER_MAXIMUM_LENGTH is not null then '(' + case when CONVERT(varchar(10), CHARACTER_MAXIMUM_LENGTH) = '-1' then 'max' else CONVERT(varchar(10), CHARACTER_MAXIMUM_LENGTH) end + ')' else '' end)
 + (case 
 when IS_NULLABLE = 'YES'
 then
 case when COLUMN_DEFAULT is null
 then ' = Null'
 else ''
 end
 else
 case when COLUMN_DEFAULT is null
 then ''
 else
 case when COLUMN_NAME = @PK_COLUMN
 then ''
 else ' = ' + replace(replace(COLUMN_DEFAULT, '(', ''), ')', '')
 end
 end
 end)
 + ',' + char(13) 
 from INFORMATION_SCHEMA.COLUMNS
 where TABLE_SCHEMA = @spSchema 
 and TABLE_NAME = @spTable
 and COLUMN_NAME <> @PK_COLUMN
 order by ORDINAL_POSITION
 set @SQL = @SQL + left(@COLUMNS, len(@COLUMNS) - 2) + char(13)
 set @SQL = @SQL + ')' + char(13)
 set @SQL = @SQL + 'AS' + char(13)
 set @SQL = @SQL + '' + char(13)
 -- step 4: add a modifications section
 set @SQL = @SQL + '-- Author: Auto' + char(13)
 set @SQL = @SQL + '-- Created: ' + convert(varchar(11), getdate(), 106) + char(13)
 set @SQL = @SQL + '-- Function: Inserts a ' + @spSchema + '.' + @spTable + ' table record' + char(13)
 set @SQL = @SQL + '' + char(13)
 set @SQL = @SQL + '-- Modifications:' + char(13)
 set @SQL = @SQL + '' + char(13)
 -- body here
 -- step 5: begins a transaction
 set @SQL = @SQL + 'begin transaction' + char(13) + char(13)
 -- step 6: begin a try
 set @SQL = @SQL + 'begin try' + char(13) + char(13) 
 set @SQL = @SQL + '-- insert' + char(13)
 -- step 7: code the insert
 set @COLUMNS = ''
 select @COLUMNS = @COLUMNS + '@' + COLUMN_NAME + ','
 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @spTable
 and COLUMN_NAME <> @PK_COLUMN
 order by ORDINAL_POSITION
 set @COLUMNS = left(@COLUMNS, len(@COLUMNS) -1) -- trim off the last comma
 set @SQL = @SQL + 'insert [' + @spSchema + '].[' + @spTable + '] (' + replace(@COLUMNS, '@', '') + ')' + char(13)
 set @SQL = @SQL + 'values (' + @COLUMNS + ')' + char(13)
 set @SQL = @SQL + char(13) + char(13)
 set @SQL = @SQL + '-- Return the new ID' + char(13)
 set @SQL = @SQL + 'select SCOPE_IDENTITY();' + char(13) + char(13)
 -- step 8: commit the transaction
 set @SQL = @SQL + 'commit transaction' + char(13) + char(13)
 -- step 9: end the try
 set @SQL = @SQL + 'end try' + char(13) + char(13)
 -- step 10: begin a catch
 set @SQL = @SQL + 'begin catch' + char(13) + char(13) 
 -- step 11: raise the error
 set @SQL = @SQL + ' declare @ErrorMessage NVARCHAR(4000);' + char(13)
 set @SQL = @SQL + ' declare @ErrorSeverity INT;' + char(13)
 set @SQL = @SQL + ' declare @ErrorState INT;' + char(13) + char(13)
 set @SQL = @SQL + ' select @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();' + char(13) + char(13)
 set @SQL = @SQL + ' raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);' + char(13) + char(13)
 set @SQL = @SQL + ' rollback transaction' + char(13) + char(13)
 -- step 11: end the catch
 set @SQL = @SQL + 'end catch;' + char(13) + char(13)
 -- step 12: return both the drop and create statements
 RETURN @SQL_DROP + '||' + @SQL
END
GO

As you can see, there is nothing hugely complicated here, just a couple of queries to the system tables to get the tables primary, columns, and defaults, then a large amount of string concatenation. Admittedly functions such as this are not fun to write but believe me, when you have used them over and over again, you'll be glad that you put the time in to start with.

The Create Update Procedure Function

This follows the same basic structure as the create insert function procedure except that it obviously codes in an update as opposed to an insert.

The Create Delete Procedure Function

This follows the same basic structure as the create insert function procedure except that it obviously codes in a delete as opposed to an insert and only takes one parameter which is the primary key.

Putting it all together

So now that we have three create functions - and hopefully a good idea of what they do - it's time to put them to work.

To do this I instantiate a memory table, fill it with the tables that I wish to create the stored procedures for, and then loop through executing the three functions to get the SQL to create the procedures which I then execute as dynamic SQL. In the below example I have coded the procedures to be created for every table in the database that has one identity primary key column but obviously you need to alter this to suit your needs.

Let's have a look at the code.

set nocount on;
-- write the sps we require
-- create the local variables
declare @spId int
declare @spSchema varchar(200)
declare @spTable varchar(200)
declare @spInsertSQL varchar(max)
declare @spUpdateSQL varchar(max)
declare @spDeleteSQL varchar(max) 
declare @dropPoint int
declare @drop_sp_sql varchar(max)
declare @create_sp_sql varchar(max)
declare @spsToWrite table(spId int identity(1,1), spSchema varchar(200), spTable varchar(200))
-- populate the list of tables to process
insert into @spsToWrite(spSchema, spTable)
select ist.TABLE_SCHEMA, ist.TABLE_NAME
from INFORMATION_SCHEMA.TABLES ist
inner join 
(
select c.table_schema, c.table_name
from information_schema.table_constraints pk 
inner join information_schema.key_column_usage c 
 on c.table_name = pk.table_name 
 and c.constraint_name = pk.constraint_name
where pk.constraint_type = 'primary key'
 and c.column_name in
 (
 select COLUMN_NAME
 from INFORMATION_SCHEMA.COLUMNS
 where columnproperty(object_id(quotename(c.table_schema) + '.' + 
 quotename(c.table_name)), COLUMN_NAME, 'IsIdentity') = 1 -- column is an identity column
 group by COLUMN_NAME
 )
group by c.table_schema, c.table_name
having count(c.column_name) = 1 -- table only has one primary key
) tables_with_one_identity_pk
 on ist.table_schema = tables_with_one_identity_pk.table_schema
 and ist.table_name = tables_with_one_identity_pk.table_name
where (ist.TABLE_TYPE = 'BASE TABLE'
 and ist.TABLE_NAME not like 'ddl%' 
 and ist.TABLE_NAME not like 'sys%') -- add any further where clause restrictions here: certain schema, specific table names, etc.
order by TABLE_NAME
begin try
 -- get the first table to process
 select @spId = (select top 1 spId from @spsToWrite order by spId)
 -- loop through each table and create the desired stored procedures
 while (@spId <> 0)
 begin
 select @spSchema = spSchema,
 @spTable = spTable
 from @spsToWrite
 where spId = @spId
 set @drop_sp_sql = ''
 set @create_sp_sql = ''
 -- write an insert procedure for this table
 set @spInsertSQL = dbo.createInsertSP(@spSchema, @spTable)
 set @dropPoint = CHARINDEX('||', @spInsertSQL)
 set @drop_sp_sql = left(@spInsertSQL, @dropPoint - 1)
 set @create_sp_sql = right(@spInsertSQL, len(@spInsertSQL) - (@dropPoint + 1))
 execute(@drop_sp_sql) -- drop any existing procedure
 execute(@create_sp_sql) -- create the new one
 --print @drop_sp_sql
 --print 'GO'
 --print @create_sp_sql
 --print 'GO'
 -- write an update procedure for this table
 set @spUpdateSQL = dbo.createUpdateSP(@spSchema, @spTable)
 set @dropPoint = CHARINDEX('||', @spUpdateSQL)
 set @drop_sp_sql = left(@spUpdateSQL, @dropPoint - 1)
 set @create_sp_sql = right(@spUpdateSQL, len(@spUpdateSQL) - (@dropPoint + 1))
 execute(@drop_sp_sql) -- drop any existing procedure
 execute(@create_sp_sql) -- create the new one
 --print @drop_sp_sql
 --print 'GO'
 --print @create_sp_sql
 --print 'GO'
 -- write a delete sp for this table
 set @spDeleteSQL = dbo.createDeleteSP(@spSchema, @spTable) -- code in delete
 set @dropPoint = CHARINDEX('||', @spDeleteSQL)
 set @drop_sp_sql = left(@spDeleteSQL, @dropPoint - 1)
 set @create_sp_sql = right(@spDeleteSQL, len(@spDeleteSQL) - (@dropPoint + 1))
 execute(@drop_sp_sql) -- drop any existing procedure
 execute(@create_sp_sql) -- create the new one
 --print @drop_sp_sql
 --print 'GO'
 --print @create_sp_sql
 --print 'GO'
 -- delete the table just processed from the working table
 delete from @spsToWrite where spId = @spId
 -- get the next table
 set @spId = 0
 select @spId = (select top 1 spId from @spsToWrite order by spId)
 end
end try
begin catch
 select ERROR_NUMBER() AS ErrorNumber,
 ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() AS ErrorState,
 ERROR_PROCEDURE() AS ErrorProcedure,
 ERROR_LINE() AS ErrorLine,
 ERROR_MESSAGE() AS ErrorMessage;
end catch
go

There are commented out print statements in the code, as you may want to just test this without creating any objects and if so, just comment out the execute lines and comment back in the print lines.

Conclusion

So there you have it, an automated solution to creating stored procedures for your tables. Obviously you will need to tweak it to fit your personal or corporate preferences but a little time invested will pay you back big time in the long run. All the necessary scripts are attached so please, give it a try.