Create CRUD Sprocs

CreateCrudSprocs.sql

/* Eric Wise 
   8/1/2012
   Building this on a lark for my own personal use, based off a crappy 
   version that didn't support a large amount of columns that my developer friend 
   tried to foist on me.
   
   It does assume that the first ordinal column is tAhe primary key.
   Insert assumes the first ordinal column is an output identity primary key
   Update assumes you have more than 1 column
   
   Yes, we do open and close the cursor quite a few times, but who cares, 
   we're only running this once in a great while... likely on a dev instance
*/

USE [YourDB]

DECLARE @TableName varchar(100)

DECLARE @CreateSelectAll bit
DECLARE @SelectAllSuffix varchar(30)
DECLARE @CreateSelectByPK bit
DECLARE @SelectByPKSuffix varchar(30)
DECLARE @CreateInsert bit
DECLARE @InsertSuffix varchar(30)
DECLARE @CreateUpdate bit
DECLARE @UpdateSuffix varchar(30)
DECLARE @CreateDelete bit
DECLARE @DeleteSuffix varchar(30)

SET @TableName = 'YourTable'
SET @CreateSelectAll = 0
SET @SelectAllSuffix = 'GetAll'
SET @CreateSelectByPK = 0
SET @SelectByPKSuffix = 'GetByID'
SET @CreateInsert = 1
SET @InsertSuffix = 'Insert'
SET @CreateUpdate = 0
SET @UpdateSuffix = 'Update'
SET @CreateDelete = 0
SET @DeleteSuffix = 'Delete'

-- if you prefix your tables and want to ignore it, set it here
DECLARE @TablePrefix varchar(10)
SET @TablePrefix = 'tbl_'

-- Declare variables which will hold values from cursor rows
DECLARE @ColumnName varchar(100), @DataType varchar(30), @CharLength int, @TableSchema varchar(30)
DECLARE @Where varchar(500)

DECLARE @ShortTableName varchar(100)
SET @ShortTableName = REPLACE(@TableName, @TablePrefix, '')

DECLARE infoCursor Cursor FOR 
SELECT c.TABLE_SCHEMA, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.Columns c INNER JOIN
	INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_NAME = @TableName
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION

IF @CreateSelectAll = 1 
BEGIN
	OPEN infoCursor
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength	
	
	
	PRINT 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @ShortTableName + @SelectAllSuffix + ']'') AND type in (N''P'', N''PC''))
	DROP PROCEDURE [dbo].[' + @ShortTableName + @SelectAllSuffix + '] 
GO
		   
SET ANSI_NULLS ON
GO
	
SET QUOTED_IDENTIFIER ON
GO
		   
CREATE PROCEDURE ' + @TableSchema + '.[' + @ShortTableName + @SelectAllSuffix +']
AS
	SET NOCOUNT ON
	
	SELECT ' + @ColumnName

	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	
	WHILE @@FETCH_STATUS = 0 
	BEGIN	
		PRINT '          ,' + @ColumnName
		FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength	
	END
	
	PRINT '    FROM ' + @TableName
	PRINT ''
	PRINT 'GO'
	PRINT ''
	
	CLOSE infoCursor	
END

IF @CreateSelectByPK = 1 
BEGIN
	OPEN infoCursor
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength	
	SET @Where = '	WHERE ' + @ColumnName + ' = @' + @ColumnName
	
	PRINT 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @ShortTableName + @SelectByPKSuffix + ']'') AND type in (N''P'', N''PC''))
	DROP PROCEDURE [dbo].[' + @ShortTableName + @SelectByPKSuffix + '] 
GO
		   
SET ANSI_NULLS ON
GO
	
SET QUOTED_IDENTIFIER ON
GO
		   
CREATE PROCEDURE ' + @TableSchema + '.[' + @ShortTableName + @SelectByPKSuffix +'](
	@' + @ColumnName + ' ' + @DataType + CASE WHEN @CharLength IS NULL THEN '' ELSE '(' + cast(@CharLength as varchar(4)) + ')' END + ' 
)
AS
	SET NOCOUNT ON
	
	SELECT ' + @ColumnName

	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	
	WHILE @@FETCH_STATUS = 0 
	BEGIN	
		PRINT '          ,' + @ColumnName
		FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength	
	END
	
	PRINT '    FROM ' + @TableName
	PRINT @Where
	PRINT ''
	PRINT 'GO'
	PRINT ''
	
	CLOSE infoCursor	
END

IF @CreateDelete = 1
BEGIN
	OPEN infoCursor
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength	
	SET @Where = '	WHERE ' + @ColumnName + ' = @' + @ColumnName
	
	PRINT 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @ShortTableName + @DeleteSuffix + ']'') AND type in (N''P'', N''PC''))
	DROP PROCEDURE [dbo].[' + @ShortTableName + @DeleteSuffix + '] 
GO
		   
SET ANSI_NULLS ON
GO
	
SET QUOTED_IDENTIFIER ON
GO
		   
CREATE PROCEDURE ' + @TableSchema + '.[' + @ShortTableName + @DeleteSuffix +'](
	@' + @ColumnName + ' ' + @DataType + CASE WHEN @CharLength IS NULL THEN '' ELSE '(' + cast(@CharLength as varchar(4)) + ')' END + ' 
)
AS
	SET NOCOUNT ON
	
	DELETE FROM ' + @TableName 
	
	PRINT @Where
	PRINT ''
	PRINT 'GO'
	PRINT ''
	
	CLOSE infoCursor	
END

IF @CreateInsert = 1
BEGIN
	DECLARE @SetIdentity varchar(100)
	
	OPEN infoCursor
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength	
	SET @SetIdentity = '	SET @' + @ColumnName + ' = SCOPE_IDENTITY();'
	
	PRINT 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @ShortTableName + @InsertSuffix + ']'') AND type in (N''P'', N''PC''))
	DROP PROCEDURE [dbo].[' + @ShortTableName + @InsertSuffix + '] 
GO
		   
SET ANSI_NULLS ON
GO
	
SET QUOTED_IDENTIFIER ON
GO
		   
CREATE PROCEDURE ' + @TableSchema + '.[' + @ShortTableName + @InsertSuffix +']('
	PRINT '    @' + @ColumnName + ' ' + @DataType + CASE WHEN @CharLength IS NULL THEN '' ELSE '(' + cast(@CharLength as varchar(4)) + ')' END + ' output'

	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		PRINT '   ,@' + @ColumnName + ' ' + @DataType + CASE WHEN @CharLength IS NULL THEN '' ELSE '(' + cast(@CharLength as varchar(4)) + ')' END
		FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	END
	
	CLOSE infoCursor
	PRINT ')
AS
	SET NOCOUNT ON
	
	INSERT INTO ' + @TableName + ' ('
	
	OPEN infoCursor
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength -- skip identity
	
	PRINT '         ' + @ColumnName
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		PRINT '        ,' + @ColumnName
		FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	END
	
	CLOSE infoCursor
	PRINT '    ) 
	VALUES ( '
	
	OPEN infoCursor
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength -- skip identity
	PRINT '         @' + @ColumnName
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		PRINT '        ,@' + @ColumnName
		FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	END
	
	PRINT '    )'
	PRINT ''
	PRINT @SetIdentity 
	PRINT 'GO'
	PRINT ''
	
	CLOSE infoCursor	
END

IF @CreateUpdate = 1
BEGIN
	OPEN infoCursor
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength	
	
	PRINT 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @ShortTableName + @UpdateSuffix + ']'') AND type in (N''P'', N''PC''))
	DROP PROCEDURE [dbo].[' + @ShortTableName + @UpdateSuffix + '] 
GO
		   
SET ANSI_NULLS ON
GO
	
SET QUOTED_IDENTIFIER ON
GO
		   
CREATE PROCEDURE ' + @TableSchema + '.[' + @ShortTableName + @UpdateSuffix +']('
	PRINT '    @' + @ColumnName + ' ' + @DataType + CASE WHEN @CharLength IS NULL THEN '' ELSE '(' + cast(@CharLength as varchar(4)) + ')' END

	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		PRINT '   ,@' + @ColumnName + ' ' + @DataType + CASE WHEN @CharLength IS NULL THEN '' ELSE '(' + cast(@CharLength as varchar(4)) + ')' END
		FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	END
	
	CLOSE infoCursor
	PRINT ')
AS
	SET NOCOUNT ON
	
	UPDATE ' + @TableName + ' SET'
	
	OPEN infoCursor
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	SET @Where = '	WHERE ' + @ColumnName + ' = @' + @ColumnName
	
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	PRINT '        ' + @ColumnName + ' = @' + @ColumnName
	FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		PRINT '       ,' + @ColumnName + ' = @' + @ColumnName
		FETCH NEXT FROM infoCursor INTO @TableSchema, @ColumnName, @DataType, @CharLength
	END
	
	CLOSE infoCursor
	PRINT @Where
	PRINT ''
	PRINT 'GO'
	PRINT ''
END

DEALLOCATE infoCursor