February 10, 2011 at 3:14 am
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;
February 10, 2011 at 3:15 am
this is the script i execute and i get that return
declare @char varchar(1000)
execute @char = dbo.createInsertSP 'autosp','tblspmatic'
select @char
February 10, 2011 at 3:16 am
@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]
February 10, 2011 at 3:29 am
@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]
February 10, 2011 at 3:37 am
James, there is a code tag you can use when posting code to make it more readable...
February 10, 2011 at 3:41 am
Apologies Kristian, I do know this as I used it to write the article! The previous post has been altered accordingly.
James
MCM [@TheSQLPimp]
February 10, 2011 at 3:50 am
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
February 10, 2011 at 3:50 am
Brilliant 😉
February 10, 2011 at 3:53 am
It's very nice.
February 10, 2011 at 4:49 am
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)
February 10, 2011 at 4:56 am
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]
February 10, 2011 at 7:00 am
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?
February 10, 2011 at 7:10 am
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
February 10, 2011 at 7:10 am
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]
February 10, 2011 at 7:39 am
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