I am often tasked with creating Create, Read, Update, and Delete (also know as CRUD) stored procedures in SQL. While researching online, I have found that there are many tools available out there. There is only one problem with the majority of these tools, they are either ridiculously hard, if not impossible to customize.
Now, the majority of the time that is not an issue as these statements are usually very generic and do not do anything special. But sometimes, I need them to produce results in a way different then they were designed. For example, some of these tools have designed their insert statements to return the entire inserted row, but for certain applications, I only want the newly created Id returned.
So I took it upon myself to create this script which will created all CRUD statements for every table in the database. I know that different people prefer to use different procedure prefix values, so that is a configuration item at the top of the script. I prefer to use the “usp_” prefix, so that is what the script defaults to, but you can change it to be whatever you like. Also, the script will create stored procedures using the same schema as the table was created with. So, if you created the table with the default “dbo” schema, the stored procedures will be created as “[dbo].[<TableName><procedure type>]” (i.e. [dbo].[TableNameInsert]) or if you created the table with a different table schema, for instance “PROD”, then the stored procedures will be created as “[PROD].[<TableName><proceduretype>]” (i.e. [PROD].[TableNameInsert])
In order to run the script all you need to do is the following:
- Bring the needed database into context (USE <Your Database>;)
- Past the script syntax into your query window
- Set the prefix value
- Execute the script
Download: SQL Server Crud Generator
SET NOCOUNT ON;
-- User Preferences
DECLARE @prefix varchar(5);
SET @prefix = 'usp_';
-- End User Preferences
-- Create CRUD procedures
DECLARE DBtables CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'u' AND name != 'sysdiagrams'
DECLARE @tblName varchar(max);
OPEN DBtables
FETCH NEXT FROM DBtables INTO @tblName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
-- Holds column attributes for each column of the table
DECLARE @tblColumns TABLE
(
Id BIGINT IDENTITY(1,1),
Name VARCHAR(max),
DataType VARCHAR(max),
MaxLength BIGINT,
IsNullable BIT,
IsAnsiPadded BIT
)
-- Holds the primary key attributes
DECLARE @PKey TABLE
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname,
KEY_SEQ smallint,
PK_NAME sysname
)
-- If the table variables currently have rows, delete them
IF ((SELECT COUNT(*) FROM @tblColumns) > 0)
BEGIN
DELETE FROM @tblColumns;
END
IF ((SELECT COUNT(*) FROM @PKey) > 0)
BEGIN
DELETE FROM @PKey;
END
-- Insert the table attributes into table variable
INSERT INTO @tblColumns (Name, DataType, MaxLength, IsNullable, IsAnsiPadded)
SELECT c.name , t.name, c.max_length, c.is_nullable, c.is_ansi_padded
FROM sys.columns AS c
INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id
WHERE (c.object_id = object_id(@tblName))
AND t.system_type_id = t.user_type_id
ORDER BY c.column_id
-- Insert the primary key attributes into table variables
INSERT INTO @PKey
exec sp_pkeys @tblName;
DECLARE @sql NVARCHAR(MAX);
DECLARE @max BIGINT;
DECLARE @min BIGINT;
DECLARE @pkeyName varchar(100);
DECLARE @ColumnName varchar(max);
DECLARE @AnsiPadded BIT;
SET @AnsiPadded = 0;
-- Check whether the current table has a primary key
IF((SELECT COUNT(*) FROM @PKey) = 0)
BEGIN
SET @pkeyName = '';
END
ELSE
BEGIN
DECLARE @schema nvarchar(128);
SELECT @schema = CAST(TABLE_SCHEMA AS NVARCHAR) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_Name = ''+ @tblName + ''
-- Get the primary key column
SELECT @pkeyName = CAST(COLUMN_NAME AS NVARCHAR) FROM @PKey WHERE TABLE_Name = CAST(@tblName as sysname)
END
DECLARE @value varchar(100);
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @pkeyName
-- Get the min Id for table variable
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
IF(LEN(@pkeyName) > 0)
BEGIN
PRINT 'Creating Procedure: [' + @schema + '].['+ @prefix + @tblName+'Select]'
-- Check the the select procedure currently exists, if so delete
SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix + +@tblName+'Select]'') IS NOT NULL' + CHAR(10)
+ 'BEGIN' + CHAR(10) + CHAR(9)
+ 'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Select]' + CHAR(10)
+ 'END' + CHAR(10)
exec sp_executesql @sql;
-- Create select procedure
SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Select]' + CHAR(10) + CHAR(9)
+ '@'+@pkeyName + ' ' + @value + CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9) + 'SELECT '
--Loop through table columns
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
SET @sql += '[' + @ColumnName +']';
SET @min += 1;
IF(@min <= @max)
BEGIN
-- Column names must be separated by a comma
SET @sql += ', ';
END
END
-- Set the table name and where conditions
SET @sql += CHAR(10) + CHAR(9) + ' FROM [' + @schema + '].[' + @tblName + ']' + CHAR(10) + CHAR(9)
SET @sql += ' WHERE (['+ @pkeyName + '] = @' + @pkeyName + ' OR @' + @pkeyName + ' IS NULL)'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;' + CHAR(10) + CHAR(10) --+ 'GO'
exec sp_executesql @sql;
-- Create Insert procedure
PRINT 'Creating Procedure: [' + @schema + '].[' + @prefix +@tblName+'Insert]'
-- Check the the insert prcedure currently exists, if so delete
SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix +@tblName+'Insert]'') IS NOT NULL' + CHAR(10)
+ 'BEGIN' + CHAR(10) + CHAR(9)
+ 'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Insert]' + CHAR(10)
+ 'END' + CHAR(10)
exec sp_executesql @sql;
SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Insert]' + CHAR(10) + CHAR(9)
-- Get the min Id for table variable
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
-- Loop through table columns
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
-- The identity column does not belong in the procedure parameters
IF(@ColumnName != @pkeyName)
BEGIN
-- Gets the value data type
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
SET @sql += '@' + @ColumnName + ' ' + @value;
-- Determines whether the datatype is ansi padded
SELECT @AnsiPadded = IsAnsiPadded FROM @tblColumns WHERE Id = @min;
IF(@AnsiPadded = 1)
BEGIN
-- Gets the ansi padded length
SELECT @value = CASE WHEN LEFT(@value, 1) = 'n' THEN (MaxLength / 2) ELSE MaxLength END FROM @tblColumns WHERE Id = @min;
SET @sql = @sql + CASE WHEN @value <> -1 THEN '(' + @value + ')' ELSE '(MAX)' END;
END
IF(@min < @max)
BEGIN
-- Parameters must be separated by a comma
SET @sql += ',' + CHAR(10) + CHAR(9)
END
END
SET @min += 1;
END
SET @sql += CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9)
+ 'INSERT INTO [' + @schema + '].['+@tblName+'] ('
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
-- Loop through table columns
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
IF(@ColumnName != @pkeyName)
BEGIN
-- Get the column data type
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
-- timestamps cannot be updated or inserted
IF(@value <> 'timestamp')
BEGIN
-- Set the insert column list
SET @sql += @ColumnName;
IF(@min < @max)
BEGIN
SET @sql += ', '
END
END
END
SET @min += 1;
END
SET @sql += ')' + CHAR(10) + CHAR(9) + 'VALUES ('
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
-- Loop through table columns
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
-- Set the insert values
IF(@ColumnName != @pkeyName)
BEGIN
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
IF(@value <> 'timestamp')
BEGIN
SET @sql += '@' + @ColumnName
IF(@min < @max)
BEGIN
SET @sql += ', '
END
END
END
SET @min += 1;
END
-- Return the current identity
SET @sql += ')' + CHAR(10) + CHAR(10) + + CHAR(9) + 'SELECT [' + @pkeyName +'] ' + CHAR(10) + CHAR(9)
+ 'FROM [' + @schema + '].['+ @tblName + ']' + CHAR(10) + CHAR(9) + 'WHERE [' + @pkeyName + '] = SCOPE_IDENTITY()'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;' + CHAR(10) --+ 'GO'
exec sp_executesql @sql;
-- Create Update procedure
PRINT 'Creating Procedure: [' + @schema + '].[' + @prefix +@tblName+'Update]'
-- Drop the procedure if it currently exists
SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix +@tblName+'Update]'') IS NOT NULL' + CHAR(10)
+ 'BEGIN' + CHAR(10) + CHAR(9)
+ 'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Update]' + CHAR(10)
+ 'END' + CHAR(10)
--+ 'GO'
exec sp_executesql @sql;
-- Create update procedure
SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Update]' + CHAR(10) + CHAR(9)
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
-- Set the parameter list
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
-- Cannot modify timestamp fields so we skip them
IF(@value <> 'timestamp')
BEGIN
-- Set parameter list and datatypes
SET @sql += '@' + @ColumnName + ' ' + @value;
SELECT @AnsiPadded = IsAnsiPadded FROM @tblColumns WHERE Id = @min;
IF(@AnsiPadded = 1)
BEGIN
SELECT @value = CASE WHEN LEFT(@value, 1) = 'n' THEN (MaxLength / 2) ELSE MaxLength END FROM @tblColumns WHERE Id = @min;
SET @sql += CASE WHEN @value <> -1 THEN '(' + @value + ')' ELSE '(MAX)' END;
END
IF(@min < @max)
BEGIN
SET @sql += ',' + CHAR(10) + CHAR(9)
END
END
SET @min += 1;
END
SET @sql += CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9)
+ 'UPDATE [' + @schema + '].['+@tblName+']' + CHAR(10) + CHAR(9) + ' SET'
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
-- Set update conditions
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
IF(@ColumnName != @pkeyName)
BEGIN
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
IF(@value <> 'timestamp')
BEGIN
SET @sql += ' ['+@ColumnName+'] = @' + @ColumnName
IF(@min < @max)
BEGIN
SET @sql += ',' + CHAR(10) + CHAR(9) + CHAR(9) + ' '
END
END
END
SET @min += 1;
END
-- Set where conditions
SET @sql += CHAR(10) + CHAR(9) + ' WHERE [' + @pkeyName + '] = @' + @pkeyName
+ CHAR(10) + +CHAR(10) + CHAR(9) + ' SELECT @' + @pkeyName + ' AS [' + @pkeyName + ']'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;' + CHAR(10)
exec sp_executesql @sql;
PRINT 'Creating Procedure: [' + @schema + '].[' + @prefix + @tblName + 'Delete]'
-- Drop Delete procedure if it currently exists
SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix +@tblName+'Delete]'') IS NOT NULL' + CHAR(10)
+ 'BEGIN' + CHAR(10) + CHAR(9)
+ 'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Delete]' + CHAR(10)
+ 'END' + CHAR(10)
exec sp_executesql @sql;
-- Get delete parameter
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @pkeyName
-- Set procedure syntax
SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Delete]' + CHAR(10) + CHAR(9)
+ '@'+@pkeyName + ' ' + @value + CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9) + 'DELETE FROM [' + @schema + '].['
+ @tblName + ']' + CHAR(10) + CHAR(9) + 'WHERE [' + @pkeyName + '] = @' + @pkeyName + CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;'
+ CHAR(10) + CHAR(10)
exec sp_executesql @sql;
END
ELSE
BEGIN
PRINT CHAR(10) + 'Table ' + @tblName + ' does not have a primary key column and therefore CRUD statements could not be generated' + CHAR(10)
END
END
-- Get the next table to alter
FETCH NEXT FROM DBtables INTO @tblName
END
-- Close cursor
CLOSE DBtables
DEALLOCATE DBtables
PRINT 'FINISHED' Note: The script was written to work with SQL 2008. If you are in a need of a CRUD generation script to work with SQL 2005 please download the attachment.
10 Responses to SQL Database CRUD Stored Procedure Generator
sir, can you provide me an SQL server 2005 version of this script, send it to my email…thank you in advance
I have attached a zip file that contains scripts that are compatible with both SQL Server 2005 and 2008. Please let us know, if you come across any issues.
Great
its works!
Thanks for two versions
Matt, thanks very much for this killer script. I am really in a time crunch to create the DAL for 12+ tables and the LAST thing I felt like doing was writing all the stored procedures. Your script saved the day and your coding style is almost exactly like how I would have typed them by hand (I’m pretty anal with my style/formatting). You are the man!
Thanks again,
Ryan
Matt,
I don’t know if you’ll agree and I guess it’s just a matter of personal preference, but you might want to default the stored procedure’s parameters to NULL for INSERT and UPDATEs if the corresponding column in the table is NULLable. I may add that functionality.
Thanks again for the awesome script,
Ryan
Soooooo amazed and thankful. I am on a project as the sole developer, and this shaves about a week of work off my plate. Thanks so much. I have already passed this link to some other devs I know.
Fabulous script! Thanks for your work on this. I used it tonight to generate the CRUD for 17 tables. Unzip, Execute, DONE!
I modified the SQL Server 2008 script to add another stored procedure called SelectList so I can retrieve all records from a table. I also created variables for the procedure names to make it easier to rename the procedures when needed. I can send the modified script to you if you’d like.
Thanks for you work on this. God Bless!
I want to generate foreign key – primary key join stored procedures. Can anybody has script to do so? Right now I am using free version of CRUD Stored Procedure Generator which has limitation of 25 columns.
Please Help.
Where are the procedures stored after runnung the script??
Hi Toni,
The procedures are stored in the default storage location, -> Programmability -> Stored Procedures.
Hope this helps!