Automatically Generate Stored Procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by James Skipwith » Automatically Generate Stored Procedures

49 posts, Page 2 of 5

««1 2 3 4 5 »»»

Automatically Generate Stored Procedures

Rate Topic

Display Mode

Topic Options

Author

Message

natedogg_0923

Posted Thursday, February 10, 2011 2:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:46 AM
Points: 5, Visits: 13

Hey hey hey

when i execute the function

Msg 537, Level 16, State 5, Procedure createInsertSP, Line 78
Invalid length parameter passed to the LEFT or SUBSTRING function.
Msg 537, Level 16, State 5, Procedure createInsertSP, Line 110
Invalid length parameter passed to the LEFT or SUBSTRING function.

this error appears...can anyone help me out...thanks i appreciate it

Post #1061780

Kristian Ask

Posted Thursday, February 10, 2011 3:00 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 3:29 AM
Points: 53, Visits: 240

It'll only create stored procedures for tables that looks like this:

CREATE TABLE [tablename] (
  identitycolumn INT IDENTITY(1,1)
  ...
)

Post #1061782

Kristian Ask

Posted Thursday, February 10, 2011 3:01 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 3:29 AM
Points: 53, Visits: 240

natedogg_0923 (2/10/2011)

I think you'll need to post the definition for the table for us to be able to help you...

Post #1061784

natedogg_0923

Posted Thursday, February 10, 2011 3:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:46 AM
Points: 5, Visits: 13

CREATE TABLE [dbo].[tblautosp](
[idprimary] [int] IDENTITY(1,1) NOT NULL,
[desc] [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]

here it is..

Post #1061790

James A Skipwith

Posted Thursday, February 10, 2011 3:07 AM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:20 AM
Points: 59, Visits: 276

The bit in the stored proc that's complaning folks is the query to retrieve the columns for the table minus the primary key.

@natedogg
What do you get if you execute the following (changing the where clause to your schema name, table name, and primary key?):

select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'YOUR_SCHEMA_NAME'
and TABLE_NAME = 'YOUR_TABLE_NAME
and COLUMN_NAME <> 'YOUR_PK_COLUMN_NAME'
order by ORDINAL_POSITION

This is an error I didn't expect and surely would only happen if you created a table with just one identity pk column in. Is this the case? If so just add another column to the table and try again!

Cheers, James

James
MCM [@TheSQLPimp]

Post #1061792

natedogg_0923

Posted Thursday, February 10, 2011 3:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:46 AM
Points: 5, Visits: 13

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;

Post #1061798

natedogg_0923

Posted Thursday, February 10, 2011 3:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:46 AM
Points: 5, Visits: 13

this is the script i execute and i get that return
declare @char varchar(1000)
execute @char = dbo.createInsertSP 'autosp','tblspmatic'
select @char

Post #1061799

James A Skipwith

Posted Thursday, February 10, 2011 3:16 AM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:20 AM
Points: 59, Visits: 276

@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]

Post #1061801

James A Skipwith

Posted Thursday, February 10, 2011 3:29 AM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:20 AM
Points: 59, Visits: 276

@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]

Post #1061807

Kristian Ask

Posted Thursday, February 10, 2011 3:37 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 3:29 AM
Points: 53, Visits: 240

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

Post #1061813

« Prev Topic | Next Topic »

49 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions