Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Automatically Generate Stored Procedures Expand / Collapse
Author
Message
Posted Thursday, February 10, 2011 2:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, February 10, 2011 3:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 12:21 PM
Points: 53, Visits: 251
It'll only create stored procedures for tables that looks like this:

CREATE TABLE [tablename] (
identitycolumn INT IDENTITY(1,1)
...
)
Post #1061782
Posted Thursday, February 10, 2011 3:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 12:21 PM
Points: 53, Visits: 251
natedogg_0923 (2/10/2011)
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


I think you'll need to post the definition for the table for us to be able to help you...
Post #1061784
Posted Thursday, February 10, 2011 3:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, February 10, 2011 3:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
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
Posted Thursday, February 10, 2011 3:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, February 10, 2011 3:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, February 10, 2011 3:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
@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
Posted Thursday, February 10, 2011 3:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
@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
Posted Thursday, February 10, 2011 3:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 12:21 PM
Points: 53, Visits: 251
James, there is a code tag you can use when posting code to make it more readable...
Post #1061813
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse