Automatically Generate Stored Procedures

  • the function return this one

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[autosp].[inserttblspmatic]') AND type in (N'P', N'PC')) DROP PROCEDURE [autosp].[inserttblspmatic]||CREATE PROC [autosp].[inserttblspmatic] ( ) AS -- Author: Auto -- Created: 19 Dec 2009 -- Function: Inserts a autosp.tblspmatic table record -- Modifications: begin transaction begin try -- insert insert [autosp].[tblspmatic] () values () -- Return the new ID select SCOPE_IDENTITY(); commit transaction end try begin catch declare @ErrorMessage NVARCHAR(4000); declare @ErrorSeverity INT; declare @ErrorState INT; select @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState); rollback transaction end catch;

  • this is the script i execute and i get that return

    declare @char varchar(1000)

    execute @char = dbo.createInsertSP 'autosp','tblspmatic'

    select @char

  • @natedogg

    Try this:

    CREATE TABLE [dbo].[tblautosp](

    [idprimary] [int] IDENTITY(1,1) NOT NULL,

    [desc_text] [nchar](10) NULL,

    CONSTRAINT [PK_tblautosp] PRIMARY KEY CLUSTERED

    (

    [idprimary] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    Shame on me but it is a bug. Your column name of "desc" is obviously also a reserved work in SQL and, as I didn't put square brackets around each column name, the dynamic sql failed. Drop your table recreate as above and you're off.

    James

    James
    MCM [@TheSQLPimp]

  • @natedogg and anyone else who wants to try this and has column names that are reserved words in sql server, revised createInsert and createUpdate procedures to cope with this:

    /*

    Insert Procedure Creation Logic

    Generates a drop if exists statement

    Generates a parameter list inclusding all columns in the table

    Generates and Insert Statement

    All wrapped in a try catch and transactional

    */

    -- set (insert\update)

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[createInsertSP]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

    DROP FUNCTION [dbo].[createInsertSP]

    GO

    CREATE FUNCTION dbo.createInsertSP

    (

    @spSchema varchar(200), -- desired schema

    @spTable varchar(200) -- desired table

    )

    RETURNS varchar(max)

    AS

    BEGIN

    declare @SQL_DROP varchar(max)

    declare @sql varchar(max)

    declare @COLUMNS varchar(max)

    declare @PK_COLUMN varchar(200)

    set @sql = ''

    set @SQL_DROP = ''

    set @COLUMNS = ''

    -- step 1: generate the drop statement and then the create statement

    set @SQL_DROP = @SQL_DROP + 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @spSchema + '].[insert' + @spTable + ']'') AND type in (N''P'', N''PC''))' + char(13)

    set @SQL_DROP = @SQL_DROP + 'DROP PROCEDURE [' + @spSchema + '].[insert' + @spTable + ']'

    set @sql = @sql + 'CREATE PROC [' + @spSchema + '].[insert' + @spTable + ']' + char(13)

    set @sql = @sql + '(' + char(13)

    -- step 2: ascertain what the primary key column for the table is

    set @PK_COLUMN =

    (

    select c.column_name

    from information_schema.table_constraints pk

    inner join information_schema.key_column_usage c

    on c.table_name = pk.table_name

    and c.constraint_name = pk.constraint_name

    where pk.TABLE_SCHEMA = @spSchema

    and pk.TABLE_NAME = @spTable

    and pk.constraint_type = 'primary key'

    and c.column_name in

    (

    select COLUMN_NAME

    from INFORMATION_SCHEMA.COLUMNS

    where columnproperty(object_id(quotename(@spSchema) + '.' +

    quotename(@spTable)), COLUMN_NAME, 'IsIdentity') = 1 -- ensure the primary key is an identity column

    group by COLUMN_NAME

    )

    group by column_name

    having COUNT(column_name) = 1 -- ensure there is only one primary key

    )

    -- step 3: now put all the table columns in bar the primary key (as this is an insert and it is an identity column)

    select @COLUMNS = @COLUMNS + '@' + COLUMN_NAME

    + ' as '

    + (case DATA_TYPE when 'numeric' then DATA_TYPE + '(' + convert(varchar(10), NUMERIC_PRECISION) + ',' + convert(varchar(10), NUMERIC_SCALE) + ')' else DATA_TYPE end)

    + (case when CHARACTER_MAXIMUM_LENGTH is not null then '(' + case when CONVERT(varchar(10), CHARACTER_MAXIMUM_LENGTH) = '-1' then 'max' else CONVERT(varchar(10), CHARACTER_MAXIMUM_LENGTH) end + ')' else '' end)

    + (case

    when IS_NULLABLE = 'YES'

    then

    case when COLUMN_DEFAULT is null

    then ' = Null'

    else ''

    end

    else

    case when COLUMN_DEFAULT is null

    then ''

    else

    case when COLUMN_NAME = @PK_COLUMN

    then ''

    else ' = ' + replace(replace(COLUMN_DEFAULT, '(', ''), ')', '')

    end

    end

    end)

    + ',' + char(13)

    from INFORMATION_SCHEMA.COLUMNS

    where TABLE_SCHEMA = @spSchema

    and TABLE_NAME = @spTable

    and COLUMN_NAME <> @PK_COLUMN

    order by ORDINAL_POSITION

    set @sql = @sql + left(@COLUMNS, len(@COLUMNS) - 2) + char(13)

    set @sql = @sql + ')' + char(13)

    set @sql = @sql + 'AS' + char(13)

    set @sql = @sql + '' + char(13)

    -- step 4: add a modifications section

    set @sql = @sql + '-- Author: Auto' + char(13)

    set @sql = @sql + '-- Created: ' + convert(varchar(11), getdate(), 106) + char(13)

    set @sql = @sql + '-- Function: Inserts a ' + @spSchema + '.' + @spTable + ' table record' + char(13)

    set @sql = @sql + '' + char(13)

    set @sql = @sql + '-- Modifications:' + char(13)

    set @sql = @sql + '' + char(13)

    -- body here

    -- step 5: begins a transaction

    set @sql = @sql + 'begin transaction' + char(13) + char(13)

    -- step 6: begin a try

    set @sql = @sql + 'begin try' + char(13) + char(13)

    set @sql = @sql + '-- insert' + char(13)

    -- step 7: code the insert

    set @COLUMNS = ''

    select @COLUMNS = @COLUMNS + '[@' + COLUMN_NAME + '],'

    from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @spTable

    and COLUMN_NAME <> @PK_COLUMN

    order by ORDINAL_POSITION

    set @COLUMNS = left(@COLUMNS, len(@COLUMNS) -1) -- trim off the last comma

    set @sql = @sql + 'insert [' + @spSchema + '].[' + @spTable + '] (' + replace(@COLUMNS, '@', '') + ')' + char(13)

    set @sql = @sql + 'values(' + replace(replace(@COLUMNS, '[', ''), ']', '') + ')' + char(13)

    set @sql = @sql + char(13) + char(13)

    set @sql = @sql + '-- Return the new ID' + char(13)

    set @sql = @sql + 'select SCOPE_IDENTITY();' + char(13) + char(13)

    -- step 8: commit the transaction

    set @sql = @sql + 'commit transaction' + char(13) + char(13)

    -- step 9: end the try

    set @sql = @sql + 'end try' + char(13) + char(13)

    -- step 10: begin a catch

    set @sql = @sql + 'begin catch' + char(13) + char(13)

    -- step 11: raise the error

    set @sql = @sql + 'declare @ErrorMessage NVARCHAR(4000);' + char(13)

    set @sql = @sql + 'declare @ErrorSeverity INT;' + char(13)

    set @sql = @sql + 'declare @ErrorState INT;' + char(13) + char(13)

    set @sql = @sql + 'select @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();' + char(13) + char(13)

    set @sql = @sql + 'raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);' + char(13) + char(13)

    set @sql = @sql + 'rollback transaction' + char(13) + char(13)

    -- step 11: end the catch

    set @sql = @sql + 'end catch;' + char(13) + char(13)

    -- step 12: return both the drop and create statements

    RETURN @SQL_DROP + '||' + @sql

    END

    GO

    /*

    Update Procedure Creation Logic

    Generates a drop if exists statement

    Generates a parameter list inclusding all columns in the table

    Generates and Update Statement

    All wrapped in a try catch and transactional

    */

    -- set (insert\update)

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[createUpdateSP]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

    DROP FUNCTION [dbo].[createUpdateSP]

    GO

    CREATE FUNCTION dbo.createUpdateSP

    (

    @spSchema varchar(200),

    @spTable varchar(200)

    )

    RETURNS varchar(max)

    AS

    BEGIN

    declare @SQL_DROP varchar(max)

    declare @sql varchar(max)

    declare @COLUMNS varchar(max)

    declare @PK_COLUMN varchar(200)

    set @sql = ''

    set @SQL_DROP = ''

    set @COLUMNS = ''

    -- generate the drop

    set @SQL_DROP = @SQL_DROP + 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @spSchema + '].[update' + @spTable + ']'') AND type in (N''P'', N''PC''))' + char(13)

    set @SQL_DROP = @SQL_DROP + 'DROP PROCEDURE [' + @spSchema + '].[update' + @spTable + ']'

    set @sql = @sql + 'CREATE PROC [' + @spSchema + '].[update' + @spTable + ']' + char(13)

    set @sql = @sql + '(' + char(13)

    -- now put all the table columns in

    set @PK_COLUMN =

    (

    select c.column_name

    from information_schema.table_constraints pk

    inner join information_schema.key_column_usage c

    on c.table_name = pk.table_name

    and c.constraint_name = pk.constraint_name

    where pk.TABLE_SCHEMA = @spSchema

    and pk.TABLE_NAME = @spTable

    and pk.constraint_type = 'primary key'

    and c.column_name in

    (

    select COLUMN_NAME

    from INFORMATION_SCHEMA.COLUMNS

    where columnproperty(object_id(quotename(@spSchema) + '.' +

    quotename(@spTable)), COLUMN_NAME, 'IsIdentity') = 1

    group by COLUMN_NAME

    )

    group by column_name

    having COUNT(column_name) = 1

    )

    select @COLUMNS = @COLUMNS + '@' + COLUMN_NAME

    + ' as '

    + (case DATA_TYPE when 'numeric' then DATA_TYPE + '(' + convert(varchar(10), NUMERIC_PRECISION) + ',' + convert(varchar(10), NUMERIC_SCALE) + ')' else DATA_TYPE end)

    + (case when CHARACTER_MAXIMUM_LENGTH is not null then '(' + case when CONVERT(varchar(10), CHARACTER_MAXIMUM_LENGTH) = '-1' then 'max' else CONVERT(varchar(10), CHARACTER_MAXIMUM_LENGTH) end + ')' else '' end)

    + (case

    when IS_NULLABLE = 'YES'

    then

    case when COLUMN_DEFAULT is null

    then ' = Null'

    else ''

    end

    else

    case when COLUMN_DEFAULT is null

    then ''

    else

    case when COLUMN_NAME = @PK_COLUMN

    then ''

    else

    case when COLUMN_NAME = @PK_COLUMN

    then ''

    else ' = ' + replace(replace(COLUMN_DEFAULT, '(', ''), ')', '')

    end

    end

    end

    end)

    + ',' + char(13)

    from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = @spSchema and TABLE_NAME = @spTable

    order by ORDINAL_POSITION

    set @sql = @sql + left(@COLUMNS, len(@COLUMNS) - 2) + char(13)

    set @sql = @sql + ')' + char(13)

    set @sql = @sql + 'AS' + char(13)

    set @sql = @sql + '' + char(13)

    -- metadata here

    set @sql = @sql + '-- Author: Auto' + char(13)

    set @sql = @sql + '-- Created: ' + convert(varchar(11), getdate(), 106) + char(13)

    set @sql = @sql + '-- Function: Create or update a ' + @spSchema + '.' + @spTable + ' table record' + char(13)

    set @sql = @sql + '' + char(13)

    set @sql = @sql + '-- Modifications:' + char(13)

    set @sql = @sql + '' + char(13)

    -- body here

    -- Update the database in a transaction

    set @sql = @sql + 'begin transaction' + char(13) + char(13)

    set @sql = @sql + 'begin try' + char(13) + char(13)

    set @sql = @sql + '-- update' + char(13)

    -- code the update

    set @COLUMNS = ''

    set @sql = @sql + 'update [' + @spSchema + '].[' + @spTable + '] set' + char(13)

    select @COLUMNS = @COLUMNS + '[' + COLUMN_NAME + '] = coalesce(@' + COLUMN_NAME + ', [' + COLUMN_NAME + ']),' + char(13)

    from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = @spSchema and TABLE_NAME = @spTable

    and COLUMN_NAME <> @PK_COLUMN

    order by ORDINAL_POSITION

    set @sql = @sql + left(@COLUMNS, len(@COLUMNS) - 2) + char(13)

    set @sql = @sql + 'where ' + @PK_COLUMN + ' = @' + @PK_COLUMN + char(13) + char(13)

    set @sql = @sql + 'select @' + @PK_COLUMN + char(13) + char(13)

    set @sql = @sql + 'commit transaction;' + char(13) + char(13)

    set @sql = @sql + 'end try' + char(13) + char(13)

    set @sql = @sql + 'begin catch' + char(13) + char(13)

    set @sql = @sql + 'declare @ErrorMessage NVARCHAR(4000);' + char(13)

    set @sql = @sql + 'declare @ErrorSeverity INT;' + char(13)

    set @sql = @sql + 'declare @ErrorState INT;' + char(13) + char(13)

    set @sql = @sql + 'select @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();' + char(13) + char(13)

    set @sql = @sql + 'raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);' + char(13) + char(13)

    set @sql = @sql + 'rollback transaction' + char(13) + char(13)

    set @sql = @sql + 'end catch;' + char(13) + char(13)

    RETURN @SQL_DROP + '||' + @sql

    END

    GO

    James
    MCM [@TheSQLPimp]

  • James, there is a code tag you can use when posting code to make it more readable...

  • Apologies Kristian, I do know this as I used it to write the article! The previous post has been altered accordingly.

    James
    MCM [@TheSQLPimp]

  • I'd like to reiterate James's earlier point. An article is intended to illuminate or inspire. It's not intended to do a reader's work for him or her. As such, James has done a great job of highlighting an effective use of "writing SQL that writes your SQL".

    Semper in excretia, suus solum profundum variat

  • Brilliant 😉

  • It's very nice.

  • Good work.

    I have used "SQL making SQL" before in generating audit trail triggers.

    Security needs to be addressed as well, by adding grants at the end.

    Maybe pull the rights from the underlying table, and/or from the rights on the existing proc (to preserve that when regenerating)

  • All good points and noted. It would be simple to retrieve the permissions the first time from the table if the procedure didn't exist and subsequently get them from the procedure (as they may have been changed by that point).

    Thanks for the feedback, it's appreciated.

    James

    James
    MCM [@TheSQLPimp]

  • Very nice!

    I didn't find two minor problems with it, however.

    In step 7, where you getting the columns your are missing the TABLE_SCHEMA column in your where clause, so there will be a problem if you have the same table name in different schemas.

    The second problem, is if you have a calculated column in your table, it will still be in the insert statement. I don't see a column in the INFORMATION_SCHEMA.COLUMNS that would indicate if a column is calculated or not. Could someone tell me how I can determine that?

  • tpepin (2/10/2011)


    ...The second problem, is if you have a calculated column in your table, it will still be in the insert statement. I don't see a column in the INFORMATION_SCHEMA.COLUMNS that would indicate if a column is calculated or not. Could someone tell me how I can determine that?

    Good point. That's just the kind of tweak I'd expect a bunch of DBAs to come up with once given a starting point like James's.

    To see if the column is a calculated one, you could look in sys.syscolumns; there's a column called "iscomputed" in there.

    Semper in excretia, suus solum profundum variat

  • Good spot! For the computed column you just need to add a bit to the where clause: and COLUMNPROPERTY([object_id] ,[object_name], 'IsComputed') = 0

    Cheers, James

    James
    MCM [@TheSQLPimp]

  • This is a notoriously icky problem (thanks for posting a new approach).

    I've used my own generator for years and it saves me a huge amount of work. But SQL Server should provide a native solution to this problem. Are there any third-party offerings that do this in the most general case (ie. multiple keys of any type)?

Viewing 15 posts - 16 through 30 (of 48 total)

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