Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Dynamic creation of Insert, Update, Delete Stored procedure Expand / Collapse
Author
Message
Posted Wednesday, July 22, 2009 11:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 17, 2009 12:49 PM
Points: 8, Visits: 25
Comments posted to this topic are about the item Dynamic creation of Insert, Update, Delete Stored procedure


Kindest Regards,

Deepthi Viswanathan Nair

Post #757979
Posted Thursday, July 23, 2009 12:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 16, 2010 4:08 AM
Points: 1, Visits: 13
Deepthi,

This SP does not create update procedure for table, if primary key is identity column, which happens to be very common scenario usually. Can you update this accordingly. Thanks!!
Post #757989
Posted Thursday, July 23, 2009 12:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 3:02 AM
Points: 31, Visits: 63
Hi Deepthi,

Nice article, but needs to be update for e.g. varchar(max).

i.e. it doesnt work with column width "MAX"

regards
Post #757992
Posted Thursday, July 23, 2009 1:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 25, 2011 3:18 AM
Points: 34, Visits: 82
shilpig (7/23/2009)
Deepthi,

This SP does not create update procedure for table, if primary key is identity column, which happens to be very common scenario usually. Can you update this accordingly. Thanks!!


More worryingly, if your PK is an identity the delete procedure does not specify a where clause!

/*-- =============================================
-- Author : dbo
-- Create date : Jul 23 2009 8:14AM
-- Description : Delete Procedure for testtable
-- Exec [dbo].[sp_testtable_delete]
-- ============================================= */
ALTER procedure [dbo].[sp_testtable_delete]

as
begin

delete from testtable


end

A nice idea though - writing boilerplate CRUD procedures can be tiresome.
Post #758001
Posted Thursday, July 23, 2009 2:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 3:02 AM
Points: 31, Visits: 63
Hey ...

I've modified it for identity column and MAX data width..
enjoy.....


/*-- =======================================================================================

--Updated by : Samardeep Singh
-- for identity columns and MAX data width of a column
-- Author : Deepthi Viswanathan Nair
--update date : July 23 2009
-- Create date : May 22 2009 3:42PM
-- Description : Generate the Insert / Update/ Delete Stored procedure script of any table
-- by passing the table name
Exec [dbo].[sp_et_create_sps_for_table] 'et_application'

-- ========================================================================================= */
ALTER PROCEDURE [dbo].[sp_et_create_sps_for_table]
@tblName Varchar(50)
AS
BEGIN

Declare @dbName Varchar(50)
Declare @insertSPName Varchar(50), @updateSPName Varchar(50), @deleteSPName Varchar(50) ;
Declare @tablColumnParameters Varchar(1000), @tableColumns Varchar(1000),@tableColumnVariables Varchar(1000);
Declare @tableCols Varchar(1000), @tablinsertParameters Varchar(1000);
Declare @space Varchar(50) ;
Declare @colName Varchar(100) ;
Declare @colVariable Varchar(100) ;
Declare @colParameter Varchar(100) ;
Declare @strSpText Varchar(8000);
Declare @updCols Varchar(2000);
Declare @delParamCols Varchar(2000);
Declare @whereCols Varchar(2000);
Set @tblName = SubString(@tblName,CharIndex('.',@tblName)+1, Len(@tblName))
Set @insertSPName = '[dbo].[sp_' + lower(@tblName) +'_insert]' ;
Set @updateSPName = '[dbo].[sp_' + lower(@tblName) +'_update]' ;
Set @deleteSPName = '[dbo].[sp_' + lower(@tblName) +'_delete]' ;
Set @space = REPLICATE(' ', 4) ;
Set @tablColumnParameters = '' ;
Set @tableColumns = '' ;
Set @tableColumnVariables = '' ;
Set @strSPText = '' ;
Set @tableCols = '' ;
Set @updCols = '' ;
Set @delParamCols = '' ;
Set @whereCols = '' ;
SET NOCOUNT ON
-- Get all columns & data types for a table

-- 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 = @tblName
-- 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=@tblName)
-- 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_Structure
-- From sysobjects , syscolumns , systypes
-- Where sysobjects.id = syscolumns.id
-- and syscolumns.xusertype = systypes.xusertype
-- and sysobjects.xtype = 'u'
-- and sysobjects.name = @tblName
-- Order by syscolumns.colid




-- 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 #tmp_PK_Structure
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(@tblName)



--if exists(select * from #tmp_PK_Structure)
--BEGIN
--print 'y'
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 = @tblName
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=@tblName)
Order by syscolumns.colid

--END
--ELSe
--BEGIN
--print 'n'
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 = @tblName
Order by syscolumns.colid
--END

/* 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 @tablColumnParameters = @tablColumnParameters + @colParameter + CHAR(13) + @space + ',' ;
Set @tableColumnVariables = @tableColumnVariables + @colVariable + CHAR(13) + @space + @space + ',' ;
Set @tableCols = @tableCols + @colName + ',' ;
Set @updCols = @updCols + @colName + ' = ' + @colVariable + CHAR(13) + @space + @space + ',' ;
Fetch Next From SpText_Cursor Into @colName, @colVariable, @colParameter
End

Close SpText_Cursor
Deallocate SpText_Cursor

/* for update parameter*/

if exists(select * from #tmp_PK_Structure)
BEGIN
Set @tablinsertParameters=''
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 @tablinsertParameters = @tablinsertParameters + @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 #tmp_PK_Structure

Open SpPKText_Cursor

Fetch Next From SpPKText_Cursor Into @colName, @colVariable, @colParameter
While @@FETCH_STATUS = 0
Begin
Set @delParamCols = @delParamCols + @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(@tablinsertParameters)>0)
Set @tablinsertParameters = LEFT(@tablinsertParameters,LEN(@tablinsertParameters)-1) ;

-- Stored procedure scripts starts here
If (LEN(@tablColumnParameters)>0)
Begin
Set @tablColumnParameters = LEFT(@tablColumnParameters,LEN(@tablColumnParameters)-1) ;
Set @tableColumnVariables = LEFT(@tableColumnVariables,LEN(@tableColumnVariables)-1) ;
Set @tableColumns = LEFT(@tableColumns,LEN(@tableColumns)-1) ;
Set @tableCols = LEFT(@tableCols,LEN(@tableCols)-1) ;
Set @updCols = LEFT(@updCols,LEN(@updCols)-1) ;

If (LEN(@whereCols)>0)
Begin
Set @whereCols = 'WHERE ' + LEFT(@whereCols,LEN(@whereCols)-4) ;
Set @delParamCols = LEFT(@delParamCols,LEN(@delParamCols)-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(@insertSPName) 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 ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @insertSPName + ' ' + @tableCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @insertSPName

if exists(select * from #tmp_PK_Structure)
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParameters
else
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablinsertParameters

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].['+@tblName +']'
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) + ''
--Print @strSPText ;
--print
Exec(@strSPText);

if (@@ERROR=0)
Print 'Procedure ' + @insertSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @insertSPName + ' 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(@updateSPName) 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 ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @updateSPName + ' ' + @tableCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @updateSPName


if exists(select * from #tmp_PK_Structure)
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablinsertParameters
else
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParameters
--Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParameters

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].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + 'SET '
Set @strSPText = @strSPText + CHAR(13) + @space + @space + ' ' + @updCols
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 ' + @updateSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @updateSPName + ' 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(@deleteSPName) 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 ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @deleteSPName + ' ' + @delParamCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @deleteSPName
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @delParamCols
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].['+@tblName +']'
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 ' + @deleteSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @deleteSPName + ' Already exists in the database. '
End
End
Drop table #tmp_Structure
Drop table #tmp_Structure1
Drop table #tmp_PK_Structure
END
Post #758025
Posted Thursday, July 23, 2009 2:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 6:59 AM
Points: 149, Visits: 51
Nice article. I use a free app for Management Studio to do this - it also does the SELECT statement and you can alter the templates. Available at http://www.ssmstoolspack.com/
Post #758036
Posted Thursday, July 23, 2009 3:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:00 AM
Points: 95, Visits: 356
Nice one! thanks for sharing.
Post #758049
Posted Thursday, July 23, 2009 5:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 28, 2009 2:09 AM
Points: 112, Visits: 196
would be better NOT to use the sp_ prefix !

this is clear in BOL
We strongly recommend that you not use the prefix sp_ in the procedure name. This prefix is used by SQL Server to designate system stored procedures.

We recommend that you do not create any stored procedures using sp_ as a prefix. SQL Server uses the sp_ prefix to designate system stored procedures. The name you choose may conflict with some future system procedure. If your application uses nonschema qualified name references and your own procedure name conflicts with a system procedure name, your application will break because the name binds to the system procedure, not your own.


regards,
Dick
Post #758114
Posted Thursday, July 23, 2009 6:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
dbaker (7/23/2009)
would be better NOT to use the sp_ prefix !

Strongly agreed. If you do not intend to put a stored procedure in the master database (which should not be done unless you have a good reason for doing so and know the consequences) then do not use the "sp_" prefix. On the rare occasion when you want to place a stored procedure in the master database (usually your home-grown administrative procedures) then you can use "sp__" with two underscores to avoid colliding with current or future Microsoft stored procedures.
Post #758134
Posted Thursday, July 23, 2009 6:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:06 AM
Points: 2, Visits: 59
I use SQL Admin Studio as it has a customizable template engine (I used it to write Delphi code to access stored procedures). It adds the following code to the top of the generated scripts so you don't have to worry about whether the stored procedure exists or not. Note that it creates the script in an editor so you can work with it before executing it.

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MyStoredProcedure]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[MyStoredProcedure]
GO

Post #758163
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse