InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 21:43:56 GMT20http://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxHiThanks for the interesting article.“rawheiser” said: “I have used "SQL making SQL" before in generating audit trail triggers.”I am considering copying this code and doing exactly that since a couple of current projects are using VS-Lightswitch (and, indirectly, Entity Framework). In that situation, stored procedures seem to be more trouble than they are worth. Triggers are the obvious way to populate audit tables.That might be a good idea for a follow-up article.Wed, 02 Jan 2013 21:32:50 GMTgsambornhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspx[quote][b]James A Skipwith (1/1/2013)[/b][hr]... Glad you liked the article. ...[/quote]It was a great inspiration. I used this for a while and then made some improvements also. First I converted this to a Dot. Net executable as all the string handling needed was easier there. So if you want to keep it strictly in SQL Server then use a CLR procedure. Secondly our coding standards and me yelling about consistant object naming allowed me to take several shortcuts others might not be able to use. That is why I have not shared my version.I look forward to reading your new series.Wed, 02 Jan 2013 08:37:20 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxAnd the compare stuff, that rocks! Although SQL Server Data Tools does a damn fine job of this too and has intellisense.Tue, 01 Jan 2013 14:05:11 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxHi Eliassal. It was nearly two years ago that I wrote the article, so please forgive me for being rusty. There are embedded line feeds in the function, so if you change your query results to be returned as text then it should make far more sense!In the time since the article was first published I have significantly improved this process; added support for deleted\audit tables, automatic procedure rebuilding via DML triggers, integration with SQL Server Data Tools, and more. I will be writing about this soon - post my MCM lab exam in ten days - and hopefully doing an hour long session on it at SQL Bits (session accepted but you never know if you will make the final cut hey).Glad you liked the article. As I say, a much improved version will be up soon. I say versions but it may well be a stairway series, as it could easily span five articles.Tue, 01 Jan 2013 14:00:43 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspx[quote][b]eliassal (1/1/2013)[/b][hr]...however, the produced code is not formated, the output comes as a single line...[/quote]I couldn't function without my RedGate tools ([url=http://www.red-gate.com/products/sql-development/sql-developer-bundle/][u]SQL Developer Bundle[/u][/url]).With RedGate SQL Prompt you just highlight that long string of code, hit CTRL-K-Y, and it's automatically formatted using the formatting standards you set up. Of all the RedGate tools, that one single function is the one I use the most--dozens of times per day. The other RedGate tools are useful too, but SQL Prompt is the one I'm addicted to. Tue, 01 Jan 2013 12:50:14 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxVery nice work, I agree with you on the fact we always need this type of functions even though EF and other tools exist.I ran your functions and it works like a charm, however, the produced code is not formated, the output comes as a single lineIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletemyTable]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[deletemyTable]||CREATE PROC [dbo].[deletemyTable] ( @id as int ) AS -- Author: Auto -- Created: 01 Jan 2013 -- Function: Delete a myTable table record -- Modifications: begin transaction begin try -- delete delete [dbo].[myTable] where id = @id commit transaction end try ............end catch;Do you get the samed thing?Thanks again and regardsTue, 01 Jan 2013 05:15:37 GMTeliassalhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxThanks for sharing... To the negative nellies, be appreciative that someone took the timeto create something that you can modify to suit your needs. If you can't use it, don't use it.Tue, 15 Feb 2011 09:00:09 GMTArmando Prato-444805http://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxVery helpful.[url]http://www.ssmstoolspack.com[/url] Could be used for achieving similar.Thu, 10 Feb 2011 17:05:18 GMTvishnukandalahttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxThanks Kevin, I'm definitely going to check this out!Yeah, I've not been tempted by the ORM route yet for the very reason you mentioned.Thanks again for the link, I know I'll be checking it out and I'm sure some other people will be too. :-)Appreciate the feedback.Cheers, JamesThu, 10 Feb 2011 12:26:31 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspx1) ORMs are getting better but can still be DEVASTATINGLY BAD for database application performance.2) one word: SSMSToolsPack!!!! FREE SSMS addin with an incredible array of useful features, including full (customizable) CRUD sproc generation. [url]http://www.ssmstoolspack.com/[/url]Thu, 10 Feb 2011 10:30:03 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxGlad you both enjoyed it and, yes, having just been put onto merge, I may well start using this.Cheers, jamesThu, 10 Feb 2011 09:57:45 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxgood read. Have you considered a merge statement version? How about using about using user defined table types?Thu, 10 Feb 2011 09:52:01 GMTemmchildhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxThis is definitely awesome! Thank you, :-) only problem is where were you three years ago when I had to write all of the insert and updates before?I haven't implemented it yet but I feel this will definetely be beneficial to our company in developement and distribution.Thank you.Thu, 10 Feb 2011 09:48:37 GMTjwbart06http://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspx[b]@pdnethercott [/b]Glad it worked for you!Yes, I too use a combined "save" procedure in a number of systems (for inserts and updates). You'll easily be able to tweak the code to do this. I did originally have it as generating one "set" procedure but then split it into sperate insert and update ones - so hard to know what an audience will want!Glad you got some benefit from the article.Cheers, JamesThu, 10 Feb 2011 09:45:03 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxChris,This could easily be built in, as you have both the parameters and the column names available in the createUpdate SP, and it would just be a question of adding and extra section to the where clause. There would be a trade-off here though between which columns you check: if they are all covered by and index then fine but if not, all columns = table scan (not good). As you say though, if you have fairly static data then you can have large covering indexes as the system is mainly read, so this will not be an issue. The advice would be to measure the volatility of the table, choose which columns you really need to check for changes, then index accordingly - oh, and obviously change the createUpdate sp code! :-)Thanks for taking the time to read the article and I hope you enjoyed it.Cheers, JamesThu, 10 Feb 2011 09:39:53 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxThank you for this great article. I'm always looking for ways to make my repetitive tasks, easier.We'll often combine inserts/updates/deletes for a given table in a single stored proc, along with the desired selections -- pass in a flag to indicate which is to be done. Using the 'print' statements rather than the 'execute()' statements gives me the pieces I need, which I can then copy into the desired stored proc.One thing users of this code should keep in mind is that you'll have to code to not include Computed fields. Once I got this code in place, worked like a charm.Thank you, again!Thu, 10 Feb 2011 09:31:40 GMTpdnethercotthttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxI've done this myself, but within a vb.net program that also generates vb code to treat it as a model. I believe later versions of Visual Studio provide this functionality.However, something I also did was have the update stored proc only modify the row if something was actually different between the new data and old. Most update procs blindly update, even though there are no changes. With my version, no update takes place, and so the transaction log doesn't get modified. This was a very specific application where I had a large amount of data with minimal differences.Thu, 10 Feb 2011 09:21:49 GMTchris 24158http://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxAlthough as I'm now heading towards VS.Net database projects, I may switch to using sql to write C# stored procedure classes!Thanks for the feedback, appreciate it.Cheers, JamesThu, 10 Feb 2011 09:07:52 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxGreat article, James. Like you, I'm all about automating things to be able to focus my time on the fun stuff:-). A few years ago I wrote a VB.NET program that queries the SYSOBJECTS, etc. tables to get the tables, fields, and field types to be able to automatically generate a script for select, update, insert, and delete stored procedures--all of which I incorporated in a program that writes VB.NET code. Nice to see that one can use SQL to write SQL as well:-).Thu, 10 Feb 2011 08:32:23 GMTmfagan12http://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxThis 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)?Thu, 10 Feb 2011 07:39:42 GMTGlen Cooperhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxGood spot! For the computed column you just need to add a bit to the where clause: and COLUMNPROPERTY([object_id] ,[object_name], 'IsComputed') = 0Cheers, JamesThu, 10 Feb 2011 07:10:42 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspx[quote][b]tpepin (2/10/2011)[/b][hr]...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?[/quote]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.Thu, 10 Feb 2011 07:10:31 GMTmajorbloodnockhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxVery 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?Thu, 10 Feb 2011 07:00:10 GMTtpepinhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxAll 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.JamesThu, 10 Feb 2011 04:56:26 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxGood 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)Thu, 10 Feb 2011 04:49:40 GMTrawheiserhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxIt's very nice.Thu, 10 Feb 2011 03:53:45 GMTtripurari.vshttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxBrilliant ;-)Thu, 10 Feb 2011 03:50:24 GMTKristian Askhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxI'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".Thu, 10 Feb 2011 03:50:14 GMTmajorbloodnockhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxApologies Kristian, I do know this as I used it to write the article! The previous post has been altered accordingly.Thu, 10 Feb 2011 03:41:36 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxJames, there is a code tag you can use when posting code to make it more readable...Thu, 10 Feb 2011 03:37:25 GMTKristian Askhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspx@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:[code="sql"]/*Insert Procedure Creation LogicGenerates a drop if exists statementGenerates a parameter list inclusding all columns in the tableGenerates and Insert StatementAll 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]GOCREATE FUNCTION dbo.createInsertSP( @spSchema varchar(200), -- desired schema @spTable varchar(200) -- desired table)RETURNS varchar(max)ASBEGIN 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 ENDGO/*Update Procedure Creation LogicGenerates a drop if exists statementGenerates a parameter list inclusding all columns in the tableGenerates and Update StatementAll 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]GOCREATE FUNCTION dbo.createUpdateSP( @spSchema varchar(200), @spTable varchar(200))RETURNS varchar(max)ASBEGIN 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 + '||' + @SQLENDGO[/code]Thu, 10 Feb 2011 03:29:35 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspx@natedoggTry 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.JamesThu, 10 Feb 2011 03:16:06 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxthis is the script i execute and i get that returndeclare @char varchar(1000)execute @char = dbo.createInsertSP 'autosp','tblspmatic'select @charThu, 10 Feb 2011 03:15:29 GMTnatedogg_0923http://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxthe function return this oneIF 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;Thu, 10 Feb 2011 03:14:37 GMTnatedogg_0923http://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxThe bit in the stored proc that's complaning folks is the query to retrieve the columns for the table minus the primary key.@natedoggWhat 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.COLUMNSwhere TABLE_SCHEMA = 'YOUR_SCHEMA_NAME' and TABLE_NAME = 'YOUR_TABLE_NAME and COLUMN_NAME <> 'YOUR_PK_COLUMN_NAME'order by ORDINAL_POSITIONThis 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, JamesThu, 10 Feb 2011 03:07:48 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxCREATE 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..Thu, 10 Feb 2011 03:06:01 GMTnatedogg_0923http://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspx[quote][b]natedogg_0923 (2/10/2011)[/b][hr]Hey hey heywhen i execute the function Msg 537, Level 16, State 5, Procedure createInsertSP, Line 78Invalid length parameter passed to the LEFT or SUBSTRING function.Msg 537, Level 16, State 5, Procedure createInsertSP, Line 110Invalid length parameter passed to the LEFT or SUBSTRING function.this error appears...can anyone help me out...thanks i appreciate it[/quote]I think you'll need to post the definition for the table for us to be able to help you...Thu, 10 Feb 2011 03:01:42 GMTKristian Askhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxIt'll only create stored procedures for tables that looks like this:[code="plain"]CREATE TABLE [tablename] ( identitycolumn INT IDENTITY(1,1) ...)[/code]Thu, 10 Feb 2011 03:00:21 GMTKristian Askhttp://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspxHey hey heywhen i execute the function Msg 537, Level 16, State 5, Procedure createInsertSP, Line 78Invalid length parameter passed to the LEFT or SUBSTRING function.Msg 537, Level 16, State 5, Procedure createInsertSP, Line 110Invalid length parameter passed to the LEFT or SUBSTRING function.this error appears...can anyone help me out...thanks i appreciate itThu, 10 Feb 2011 02:56:43 GMTnatedogg_0923http://www.sqlservercentral.com/Forums/Topic1061692-2902-1.aspx[quote][b]Rahul The Dba (2/9/2011)[/b][hr]It seems that you are well famaliar in SQL, so can you tell is it always needed to have a primary key, in table and is it needed to have a identity primary key?what happens if i have no primary key or have a primary key but a non-identity one?your code will not work.Still i appreciate your hard work[/quote]You should always have a primary key on a table.Thu, 10 Feb 2011 02:52:01 GMTSamuel Vella