Dynamic Creation Of Insert Update Delete Stored Procedure in Sql Server

  
DECLARE
@TABLENAME Varchar(50) ='TABLENAME'
BEGIN
DECLARE @DBNAME Varchar(50) 
DECLARE @INSERT_SP_NAME Varchar(50),
@UPDATE_SP_NAME Varchar(50), 
@DELETE_SP_NAME Varchar(50) 
DECLARE @TABLECOLUMNPARAMETER VARCHAR(MAX)='', 
@TABLECOLUMNS Varchar(MAX)='',
@TABLECOLUMNVARIABLES Varchar(MAX)='';
DECLARE @TABLECOLS Varchar(MAX)='', 
@TABLEINSERTPARAMETER Varchar(MAX)='';
DECLARE @SPACE Varchar(50)=REPLICATE(' ', 4)
DECLARE @COLNAME Varchar(100) ;
DECLARE @COLVARIABLE Varchar(100) ;
DECLARE @COLPARAMETER Varchar(100) ;
DECLARE @STRSPTEXT Varchar(MAX)='';
DECLARE @UPDATECOLS Varchar(MAX)='';
DECLARE @DELETEPARACOLS Varchar(MAX)='';
DECLARE @WHERECOLS Varchar(MAX)='';
Set @TABLENAME = SubString(@TABLENAME,CharIndex('.',@TABLENAME)+1, Len(@TABLENAME))
Set @INSERT_SP_NAME = '[dbo].[sp_' + lower(@TABLENAME) +'_insert]' ;
Set @UPDATE_SP_NAME = '[dbo].[sp_' + lower(@TABLENAME) +'_update]' ;
Set @DELETE_SP_NAME = '[dbo].[sp_' + lower(@TABLENAME) +'_delete]' ;
SET NOCOUNT ON 

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME=@TABLENAME) 
BEGIN
    PRINT 'Sorry!! Table Name ( ' + @TABLENAME + ' ) doe''s exists in the database. '
END
------------------------------------------ Get all Primary KEY columns & Data Types For a table ------------------------------------------------------
SELECT  t.name as 'Table', 
    c.colid ,
'[' + c.name + ']' as 'ColumnName',
'@'+c.name as 'ColumnVariable',
systypes.name + 
CASE When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),c.length) +')' Else '' end as 'DataType' ,
'@'+c.name + ' ' + systypes.name + 
CASE When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),c.length) +')' Else '' end as 'ColumnParameter'
INTO #TEMP_PK_TABLE 
FROM sysindexes i, sysobjects t, sysindexkeys k, syscolumns c, systypes
WHERE i.id = t.id AND
i.indid = k.indid AND i.id = k.ID And
c.id = t.id AND c.colid = k.colid AND 
i.indid BETWEEN 1 And 254 AND 
c.xusertype = systypes.xusertype AND
(i.status & 2048) = 2048 AND t.id = OBJECT_ID(@TABLENAME)
 
SELECT distinct
sysobjects.name as 'Table', syscolumns.colid ,'[' + syscolumns.name + ']' as 'ColumnName',
'@'+syscolumns.name as 'ColumnVariable',systypes.name + 
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' 
ELSE '' end as 'DataType' ,
'@'+syscolumns.name + ' ' + systypes.name + 
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' 
ELSE '' end as 'ColumnParameter'
Into #tmp_Structure 
From sysobjects , syscolumns , systypes
Where sysobjects.id = syscolumns.id
and syscolumns.xusertype = systypes.xusertype and sysobjects.xtype = 'u' and sysobjects.name = @TABLENAME AND syscolumns.name !=
(
SELECT c.name AS ColumnName FROM sys.columns AS c INNER JOIN  sys.tables AS t ON t.[object_id] = c.[object_id] WHERE c.is_identity = 1 AND t.name=@TABLENAME
)
ORDER BY syscolumns.colid 
 
SELECT distinct
sysobjects.name as 'Table', 
syscolumns.colid ,
'[' + syscolumns.name + ']' as 'ColumnName',
'@'+syscolumns.name as 'ColumnVariable',
systypes.name + 
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')'
ELSE '' end as 'DataType' ,'@'+syscolumns.name + ' ' + systypes.name + 
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' 
ELSE '' end as 'ColumnParameter'
Into #tmp_Structure1 
From sysobjects , syscolumns , systypes
Where sysobjects.id = syscolumns.id
and syscolumns.xusertype = systypes.xusertype and sysobjects.xtype = 'u' and sysobjects.name = @TABLENAME 
ORDER by syscolumns.colid
/* Read the table structure and populate variables*/
DECLARE SpText_Cursor Cursor For
Select ColumnName, ColumnVariable, ColumnParameter
From #tmp_Structure 
Open SpText_Cursor
Fetch Next From SpText_Cursor Into @COLNAME, @COLVARIABLE, @COLPARAMETER
WHILE @@FETCH_STATUS = 0
Begin
SET @TABLECOLUMNS = @TABLECOLUMNS + @COLNAME + CHAR(13) + @SPACE + @SPACE + ',' ; 
SET @TABLECOLUMNPARAMETER = @TABLECOLUMNPARAMETER + @COLPARAMETER + CHAR(13) + @SPACE + ',' ; 
SET @TABLECOLUMNVARIABLES = @TABLECOLUMNVARIABLES + @COLVARIABLE + CHAR(13) + @SPACE + @SPACE + ',' ; 
SET @TABLECOLS = @TABLECOLS + @COLNAME + ',' ; 
SET @UPDATECOLS = @UPDATECOLS + @COLNAME + ' = ' + @COLVARIABLE + CHAR(13) + @SPACE + @SPACE + ',' ; 
Fetch Next From SpText_Cursor Into @COLNAME, @COLVARIABLE, @COLPARAMETER 
End
Close SpText_Cursor
Deallocate SpText_Cursor
---------------------------------------------------------- Update Parameter -------------------------------------------------------
if exists(select * from #TEMP_PK_TABLE)
BEGIN
SET @TABLEINSERTPARAMETER=''

DECLARE SpText_Cursor1 Cursor For
SELECT ColumnParameter
FROM #tmp_Structure1 
Open SpText_Cursor1
FETCH NEXT FROM SpText_Cursor1 Into @COLPARAMETER
While @@FETCH_STATUS = 0
BEGIN
SET @TABLEINSERTPARAMETER = @TABLEINSERTPARAMETER + @COLPARAMETER + CHAR(13) + @SPACE + ',' ; 
FETCH Next From SpText_Cursor1 Into @COLPARAMETER 
END 
CLOSE SpText_Cursor1
DEALLOCATE  SpText_Cursor1
END
----------------------------------------------------------- End for update parameter -----------------------------------------------------

----------------------------------------------------------- Read the Primary Keys from the table and populate variables ------------------
DECLARE SpPKText_Cursor Cursor For
SELECT ColumnName, ColumnVariable, ColumnParameter
FROM #TEMP_PK_TABLE 
OPEN SpPKText_Cursor
FETCH Next From SpPKText_Cursor Into @COLNAME, @COLVARIABLE, @COLPARAMETER
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DELETEPARACOLS = @DELETEPARACOLS + @COLPARAMETER + CHAR(13) + @SPACE + ',' ; 
SET @WHERECOLS = @WHERECOLS + @COLNAME + ' = ' + @COLVARIABLE + ' AND ' ; 
FETCH Next From SpPKText_Cursor Into @COLNAME, @COLVARIABLE, @COLPARAMETER 
END
Close SpPKText_Cursor
Deallocate SpPKText_Cursor

IF (LEN(@TABLEINSERTPARAMETER)>0)
SET @TABLEINSERTPARAMETER = LEFT(@TABLEINSERTPARAMETER,LEN(@TABLEINSERTPARAMETER)-1) ;
-------------------------------------------------------------- Stored procedure scripts starts here ----------------------------------------------------------
IF (LEN(@TABLECOLUMNPARAMETER)>0)
BEGIN 
Set @TABLECOLUMNPARAMETER = LEFT(@TABLECOLUMNPARAMETER,LEN(@TABLECOLUMNPARAMETER)-1) ;
Set @TABLECOLUMNVARIABLES = LEFT(@TABLECOLUMNVARIABLES,LEN(@TABLECOLUMNVARIABLES)-1) ;
Set @TABLECOLUMNS = LEFT(@TABLECOLUMNS,LEN(@TABLECOLUMNS)-1) ;
Set @TABLECOLS = LEFT(@TABLECOLS,LEN(@TABLECOLS)-1) ;
SET @UPDATECOLS = LEFT(@UPDATECOLS,LEN(@UPDATECOLS)-1) ;
IF (LEN(@WHERECOLS)>0)
BEGIN 
SET @WHERECOLS = 'WHERE ' + LEFT(@WHERECOLS,LEN(@WHERECOLS)-4) ;
SET @DELETEPARACOLS = LEFT(@DELETEPARACOLS,LEN(@DELETEPARACOLS)-1) ;
END
----------------------------------------------------------Create INSERT stored procedure for the table if it does not exist -------------------------------------
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@INSERT_SP_NAME) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + '/*-- ============================================='
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Author : dbo'
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Description : Insert Procedure for ' + @TABLENAME
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Exec ' + @INSERT_SP_NAME + ' ' + @TABLECOLS
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- ============================================= */'
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'CREATE PROCEDURE ' + @INSERT_SP_NAME

IF EXISTS(SELECT * FROM #TEMP_PK_TABLE)
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + ' ' + @TABLECOLUMNPARAMETER

ELSE

SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + ' ' + @TABLEINSERTPARAMETER
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'AS'
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'BEGIN'
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + 'INSERT INTO [dbo].['+@TABLENAME +']' 
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + '( ' 
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + @SPACE + ' ' + @TABLECOLUMNS 
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + ')'
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + 'VALUES'
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + '('
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + @SPACE + ' ' + @TABLECOLUMNVARIABLES
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + ')'
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'END'
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''
Set @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''
EXEC(@STRSPTEXT);
IF (@@ERROR=0) 
PRINT 'Procedure ' + @INSERT_SP_NAME + ' Created Successfully '
END
ELSE
BEGIN
PRINT 'Sorry!! ' + @INSERT_SP_NAME + ' Already exists in the database. '
END

------------------------------------------------------------- Create UPDATE stored procedure for the table if it does not exist ------------------------------------
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@UPDATE_SP_NAME) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
SET @STRSPTEXT = ''
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '/*-- ============================================='
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Author : dbo'
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Description : Update Procedure for ' + @TABLENAME
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Exec ' + @UPDATE_SP_NAME + ' ' + @TABLECOLS
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- ============================================= */'
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'CREATE PROCEDURE ' + @UPDATE_SP_NAME

IF exists(select * from #TEMP_PK_TABLE)
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + ' ' + @TABLEINSERTPARAMETER
ELSE
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + ' ' + @TABLECOLUMNPARAMETER
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'AS'
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'BEGIN'
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + 'UPDATE [dbo].['+@TABLENAME +']' 
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + 'SET ' 
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + @SPACE + ' ' + @UPDATECOLS 
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + @WHERECOLS
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'END'
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''

--Print @strSPText ;
EXEC(@STRSPTEXT);
IF (@@ERROR=0) 
PRINT 'Procedure ' + @UPDATE_SP_NAME + ' Created Successfully '
END
ELSE
BEGIN
PRINT 'Sorry!! ' + @UPDATE_SP_NAME + ' Already exists in the database. '
END

------------------------------------------------------ Create DELETE stored procedure for the table if it does not exist ----------------------------------
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@DELETE_SP_NAME) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
SET @STRSPTEXT = ''
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '/*-- ============================================='
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Author : dbo'
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Description : Delete Procedure for ' + @TABLENAME
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- Exec ' + @DELETE_SP_NAME + ' ' + @DELETEPARACOLS
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + '-- ============================================= */'
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'CREATE PROCEDURE ' + @DELETE_SP_NAME
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + ' ' + @DELETEPARACOLS
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'AS'
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'BEGIN'
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + 'DELETE FROM [dbo].['+@TABLENAME +']' 
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + @SPACE + @WHERECOLS
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + 'END'
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''
SET @STRSPTEXT = @STRSPTEXT + CHAR(13) + ''

EXEC(@STRSPTEXT);
IF (@@ERROR=0) 
PRINT 'Procedure ' + @DELETE_SP_NAME + ' Created Successfully '
END
ELSE
BEGIN
PRINT 'Sorry!! ' + @DELETE_SP_NAME + ' Already exists in the database. '
END
END
Drop table #tmp_Structure
Drop table #tmp_Structure1
Drop table #TEMP_PK_TABLE
END