Generate Insert/Update/Delete/Get SPROCS
This is a script that creates Insert / Update / Delete and Get stored procedures for a specific table.
It will only work for tables with a unique primary key.
Have a look at what is does, and if you like it, Vote for it.
Maybe there are tons of sprocs out there that perform the same actions but I could not find it anywhere
SET NOCOUNT ON
-- DECLARE THE NAME OF THE TABLE
DECLARE @TBL SYSNAME
SET @TBL = 'RESOURCES'
-- DECLARE THE VARIABLES
DECLARE @MAXITEMS AS INTEGER
DECLARE @COUNTER AS INTEGER
DECLARE @COLUMNNAME AS SYSNAME
DECLARE @COLUMNTYPE AS SYSNAME
DECLARE @ISINPRIKEY AS BIT
DECLARE @ANDVAR AS INTEGER
DECLARE @COUNTPRIKEYS AS INTEGER
-- CREATE AN ARRAY TO HOLD THE TABLE FIELDS
DECLARE @MEMARRAY TABLE
( ID INT IDENTITY,
COLUMN_NAME SYSNAME,
COLUMN_TYPE SYSNAME,
PRIMARYKEYFIELD BIT,
MEMVAR_NAME SYSNAME,
CCOLUMN_NAME SYSNAME)
-- FILL THE MEMORY TABLE WITH ALL THE FIELDS
INSERT @MEMARRAY (COLUMN_NAME,COLUMN_TYPE,PRIMARYKEYFIELD,MEMVAR_NAME,CCOLUMN_NAME) SELECT SC.NAME AS 'COLUMNNAME',
CASE BT.NAME
WHEN 'INT' THEN 'INTEGER'
WHEN 'IMAGE'THEN 'IMAGE'
WHEN 'TEXT' THEN 'TEXT'
WHEN 'VARCHAR' THEN 'VARCHAR('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
WHEN 'NVARCHAR' THEN 'NVARCHAR('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
WHEN 'NCHAR' THEN 'NCHAR('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
WHEN 'CHAR' THEN 'CHAR('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
WHEN 'VARBINARY' THEN 'VARBINARY('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
ELSE TD.NAME
END AS 'COLUMNTYPE',0,'',''
FROM SYSCOLUMNS SC
LEFT JOIN SYSTYPES TD ON TD.XUSERTYPE = SC.XUSERTYPE -- AND SYSTYPES.TYPE = SYSTYPES.XTYPE
LEFT JOIN SYSTYPES BT ON BT.XUSERTYPE = TD.XTYPE
WHERE ID = OBJECT_ID(@TBL)
ORDER BY SC.COLID
-- FIGURE OUT IF THERE IS A PRIMARY KEY. THERE SHOULD BE ONE FOR THIS TO WORK.
DECLARE @PK_INDEX SYSNAME
DECLARE @PK_INDID INTEGER
DECLARE @PK_ID AS INTEGER
SELECT @PK_INDEX=NAME FROM SYSOBJECTS WHERE XTYPE='PK' AND PARENT_OBJ = OBJECT_ID(@TBL)
SELECT @PK_INDID = INDID , @PK_ID=ID FROM SYSINDEXES WHERE NAME = @PK_INDEX
-- FIGURE OUT THE COLUMNS IN THE PRIMARY KEY
DECLARE @PK_COLUMNS AS SYSNAME
SET @PK_COLUMNS=''
SET @COUNTER = 1
WHILE INDEX_COL(@TBL,@PK_INDID,@COUNTER) IS NOT NULL
BEGIN
SELECT @PK_COLUMNS=@PK_COLUMNS + '#'+ INDEX_COL(@TBL,@PK_INDID,@COUNTER) + '#,'
SET @COUNTER = @COUNTER + 1
END
-- SET THE PRIMARYKEYFIELD IN THE MEMARRAY
SELECT @MAXITEMS = MAX(ID) FROM @MEMARRAY
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD FROM @MEMARRAY WHERE ID = @COUNTER
IF CHARINDEX('#'+@COLUMNNAME+'#',@PK_COLUMNS) > 0
BEGIN
UPDATE @MEMARRAY SET PRIMARYKEYFIELD = 1 WHERE ID = @COUNTER
END
SET @COUNTER=@COUNTER + 1
END
-- PATCH THE FIELDS MEMVAR_NAME AND CCOLUMN_NAME
DECLARE @MEMVARNAME SYSNAME
DECLARE @CCOLUMNNAME SYSNAME
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
--REPLACE SPACES IN MEMVAR NAME
SET @MEMVARNAME = '@'+REPLACE(@COLUMNNAME,' ','_')
SET @CCOLUMNNAME = '['+ @COLUMNNAME+']'
UPDATE @MEMARRAY SET MEMVAR_NAME = @MEMVARNAME,CCOLUMN_NAME=@CCOLUMNNAME WHERE ID = @COUNTER
SET @COUNTER=@COUNTER + 1
END
PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO INSERT RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED ON '+CONVERT(VARCHAR(20),GETDATE())
PRINT '-- ============================================='
PRINT 'CREATE PROCEDURE GS_INSERT_'+@TBL
PRINT '('
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 0 -- SKIP FIELDS IN THE PRIMARY KEY
BEGIN
IF @COUNTER < @MAXITEMS
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+','
ELSE
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE
END
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT 'AS'
PRINT 'INSERT '+@TBL + ' ('
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 0 -- SKIP FIELDS IN THE PRIMARY KEY
BEGIN
IF @COUNTER < @MAXITEMS
PRINT ' '+@CCOLUMNNAME+','
ELSE
PRINT ' '+@CCOLUMNNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT 'VALUES ('
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 0 -- SKIP FIELDS IN THE PRIMARY KEY
BEGIN
IF @COUNTER < @MAXITEMS
PRINT ' '+@MEMVARNAME+','
ELSE
PRINT ' '+@MEMVARNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT 'GO'
PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO UPDATE RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED ON '+CONVERT(VARCHAR(20),GETDATE())
PRINT '-- ============================================='
PRINT 'CREATE PROCEDURE GS_UPDATE_'+@TBL
PRINT '('
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @COUNTER < @MAXITEMS
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+','
ELSE
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT 'AS'
PRINT 'UPDATE '+@TBL + ' SET '
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 0 -- SKIP FIELDS IN THE PRIMARY KEY
BEGIN
IF @COUNTER < @MAXITEMS
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME + ','
ELSE
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT 'WHERE'
SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
SET @COUNTER = 1
SET @COUNTPRIKEYS = 0
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 1
BEGIN
SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
IF @COUNTPRIKEYS < @ANDVAR
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME + ' AND'
ELSE
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT 'GO'
PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO DELETE RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED ON '+CONVERT(VARCHAR(20),GETDATE())
PRINT '-- ============================================='
PRINT 'CREATE PROCEDURE GS_DELETE_'+@TBL
PRINT '('
SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
SET @COUNTER = 1
SET @COUNTPRIKEYS = 0
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 1
BEGIN
SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
IF @COUNTPRIKEYS < @ANDVAR
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+','
ELSE
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE
END
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT 'AS'
PRINT 'DELETE FROM '+@TBL
PRINT 'WHERE'
SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
SET @COUNTER = 1
SET @COUNTPRIKEYS = 0
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 1
BEGIN
SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
IF @COUNTPRIKEYS < @ANDVAR
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME + ' AND'
ELSE
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT 'GO'
PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO GET RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED ON '+CONVERT(VARCHAR(20),GETDATE())
PRINT '-- ============================================='
PRINT 'CREATE PROCEDURE GS_GET_'+@TBL
PRINT '('
SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
SET @COUNTER = 1
SET @COUNTPRIKEYS = 0
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 1
BEGIN
SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
IF @COUNTPRIKEYS < @ANDVAR
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+','
ELSE
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE
END
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT 'AS'
PRINT 'SELECT '
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
BEGIN
IF @COUNTER < @MAXITEMS
PRINT ' '+@CCOLUMNNAME+','
ELSE
PRINT ' '+@CCOLUMNNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT 'FROM '+@TBL
PRINT 'WHERE'
SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
SET @COUNTER = 1
SET @COUNTPRIKEYS = 0
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 1
BEGIN
SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
IF @COUNTPRIKEYS < @ANDVAR
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME + ' AND'
ELSE
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT 'GO' 
