Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automatically Generate Stored Procedures


Automatically Generate Stored Procedures

Author
Message
natedogg_0923
natedogg_0923
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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
Kristian Ask
Kristian Ask
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 251
It'll only create stored procedures for tables that looks like this:


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

Kristian Ask
Kristian Ask
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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...
natedogg_0923
natedogg_0923
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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..
James A Skipwith
James A Skipwith
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 640
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]
natedogg_0923
natedogg_0923
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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;
natedogg_0923
natedogg_0923
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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
James A Skipwith
James A Skipwith
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 640
@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]
James A Skipwith
James A Skipwith
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 640
@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]
Kristian Ask
Kristian Ask
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 251
James, there is a code tag you can use when posting code to make it more readable...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search