Creating insert, update, and delete stored procedures for tables in SQL Server can be a time-consuming task, especially when dealing with tables that have numerous fields. However, there is a way to automate this process and save valuable time and effort. In this article, we will explore a stored procedure that dynamically generates these basic stored procedures for a given table.

Dynamic Script Generation

The stored procedure, sp_et_create_sps_for_table, uses system tables in SQL Server to gather information about the columns of a table. By joining the sysobjects, syscolumns, and systypes tables, it retrieves the column names, data types, and other necessary information. This dynamic script generation allows for the creation of insert, update, and delete stored procedures without the need for manual coding.

Here is an example of how the stored procedure retrieves the column information:

-- 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), syscolumns.length) + ')'
                    ELSE ''
                END AS 'DataType',
                '@' + syscolumns.name + '  ' + systypes.name + CASE WHEN systypes.xusertype IN (165,167,175,231,239) THEN 
                    '(' + CONVERT(VARCHAR(10), syscolumns.length) + ')'
                    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

Primary Key Information

In addition to retrieving column information, the stored procedure also gathers the primary key information of the table. This information is essential for the update and delete stored procedures, as it is used to identify the rows to update or delete. By joining the sysindexes, sysindexkeys, and other system tables, the primary key information is retrieved and stored in a temporary table for further script generation.

Here is an example of how the primary key information is retrieved:

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

Usage

To use the stored procedure, simply provide the name of the table for which you want to create the insert, update, and delete stored procedures. Here is an example:

EXEC [dbo].[sp_et_create_sps_for_table] 'YourTableName'

The stored procedure will generate the scripts for the stored procedures and execute them. If the stored procedures already exist in the database, an error message will be displayed. In that case, you can drop the existing stored procedures and create them again by executing the stored procedure.

Conclusion

Automating the creation of basic stored procedures for tables in SQL Server can save a significant amount of time and effort, especially when dealing with a large number of tables. By using the sp_et_create_sps_for_table stored procedure, you can dynamically generate insert, update, and delete stored procedures based on the table name. This approach is flexible and customizable, allowing you to adapt it to your specific needs. Give it a try and streamline your stored procedure creation process!