Dynamic creation of Insert, Update, Delete Stored procedure

  • Deepthi Viswanathan Nair

    Old Hand

    Points: 392

    Comments posted to this topic are about the item Dynamic creation of Insert, Update, Delete Stored procedure


    Kindest Regards,

    Deepthi Viswanathan Nair

  • shilpig

    SSC Rookie

    Points: 33

    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!!

  • SAMARDEEP

    Right there with Babe

    Points: 727

    Hi Deepthi,

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

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

    regards

  • lokkers

    SSC Enthusiast

    Points: 158

    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.

  • SAMARDEEP

    Right there with Babe

    Points: 727

    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

  • john.sims

    Old Hand

    Points: 335

    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/

  • Pyay Nyein

    Mr or Mrs. 500

    Points: 565

    Nice one! thanks for sharing.

  • dbaker-620086

    SSCrazy

    Points: 2336

    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

  • Noel McKinney

    Hall of Fame

    Points: 3377

    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.

  • ddunn-1115368

    SSC Journeyman

    Points: 80

    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

  • adi - doccolabs.com

    SSC Veteran

    Points: 263

    Nice article.

    Wouldn't you rather use MERGE instead?

  • megasoft2

    Grasshopper

    Points: 18

    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!

  • Adrian Hains

    Ten Centuries

    Points: 1322

    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.

  • bkubicek

    SSChampion

    Points: 10702

    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

  • david.wright-948385

    SSCarpal Tunnel

    Points: 4026

    ... or see http://www.ssmstoolspack.com/Main.aspx

Viewing 15 posts - 1 through 15 (of 50 total)

You must be logged in to reply to this topic. Login to reply