July 22, 2009 at 11:29 pm
Comments posted to this topic are about the item Dynamic creation of Insert, Update, Delete Stored procedure
Deepthi Viswanathan Nair
July 23, 2009 at 12:22 am
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!!
July 23, 2009 at 12:35 am
Hi Deepthi,
Nice article, but needs to be update for e.g. varchar(max).
i.e. it doesnt work with column width "MAX"
regards
July 23, 2009 at 1:19 am
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.
July 23, 2009 at 2:32 am
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 @spaceVarchar(50) ;
Declare @colName Varchar(100) ;
Declare @colVariableVarchar(100) ;
Declare @colParameterVarchar(100) ;
Declare @strSpTextVarchar(8000);
Declare @updColsVarchar(2000);
Declare @delParamColsVarchar(2000);
Declare @whereColsVarchar(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
--Fromsysobjects , syscolumns , systypes
--Wheresysobjects.id = syscolumns.id
--and syscolumns.xusertype = systypes.xusertype
--and sysobjects.xtype = 'u'
--and sysobjects.name = @tblName
--and syscolumns.name!=(SELECT
--c.name AS ColumnName
--FROMsys.columns AS c
--INNER JOIN
--sys.tables AS t
--ON t.[object_id] = c.[object_id]
--wherec.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
--Fromsysobjects , syscolumns , systypes
--Wheresysobjects.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
SELECTt.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
WHEREi.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
Fromsysobjects , syscolumns , systypes
Wheresysobjects.id = syscolumns.id
and syscolumns.xusertype = systypes.xusertype
and sysobjects.xtype = 'u'
and sysobjects.name = @tblName
and syscolumns.name!=(SELECT
c.name AS ColumnName
FROMsys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
wherec.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
Fromsysobjects , syscolumns , systypes
Wheresysobjects.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 ;
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
July 23, 2009 at 2:53 am
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/
July 23, 2009 at 3:15 am
Nice one! thanks for sharing.
July 23, 2009 at 5:38 am
would be better NOT to use the sp_ prefix !
this is clear in BOL
[font="Courier New"]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.[/font]
regards,
Dick
July 23, 2009 at 6:03 am
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.
July 23, 2009 at 6:50 am
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
July 23, 2009 at 7:07 am
Nice article.
Wouldn't you rather use MERGE instead?
July 23, 2009 at 7:21 am
truth, it does not work well with any table!
this is the message:
Procedure [dbo].[sp_iv00103_insert] Created Successfully
Msg 8102, Level 16, State 1, Procedure sp_et_create_sps_for_table, Line 189
Cannot update identity column 'DEX_ROW_ID'.
Procedure [dbo].[sp_iv00103_delete] Created Successfully
thanks!
July 23, 2009 at 7:35 am
We have a very similar set of dynamic sproc creation sprocs at my work. I find them useful in a general sense but (1) hard to look at and (2) hard to conditionally customize. I think this is a good example of something that can be implemented with greater clarity in c#. You would want similar metadata queries to get the table information, but I think applying these to a template would look tons better in app code. It could be a clr UDF if you wanted to keep it in the database, but I think the ideal solution would be to query metadata with FOR XML and then pass that XML into a standalone app. I definitely have servers that I'm prohibited from enabling CLR on, but I can obviously still run the FOR XML query.
July 23, 2009 at 8:03 am
There is an issue with your proc. You use varchar(1000) and varchar(2000), the problem with this is with large tables this is not large enough. I changed all of those to varchar(max) and it works fine.
One more thing missing is a select. You have the insert, update and delete, but select is also needed.
Ben
July 23, 2009 at 8:15 am
... or see http://www.ssmstoolspack.com/Main.aspx
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply