InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 17:16:29 GMT20http://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxI just want to thank you very much for relieving HOURS of tedium! Even if I have to tweak it a bit (for example, on my updates I add ISNULL(@NewValue,[OldValue]) to the statements so I can do a partial update if I like) I will benefit greatly. I was growing old copying and pasting and typing just to get the procs inplace so I could get to the coding, which is using Entity framework. This really needed doing.:-)Lisa Z. MorganFri, 03 Jun 2011 13:08:06 GMTJoseph M. Morganhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxHi,For download and information on the code generating software for Sql Server 2005/2008, you can now visit [url=http://www.raisintech.com/SSCodeGen/tabid/87/Default.aspx]http://www.raisintech.com/SSCodeGen/tabid/87/Default.aspx[/url]. The link for the version to work with Sql Server 7 and 2000 is at this URL as well.RegardsMon, 26 Jul 2010 03:49:11 GMTanthonyhhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspx[quote][b]Eric_AZ (7/14/2010)[/b][hr]This is AWESOME! Thank you![/quote]Hi,Just wondering if you mean the SSCodeGen software to generate code, from the forum link before your reply, or this article for dynamic sql?Sat, 24 Jul 2010 09:46:13 GMTanthonyhhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxto check your decimal(p,s) you can do this :[code="sql"] Case When systypes.xusertype =106 Then '(' + Convert(varchar(10),syscolumns.xprec) +','+Convert(varchar(10),syscolumns.xscale)+')' [/code]Thu, 15 Jul 2010 07:10:28 GMTR-CHERIF Ammarhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxThis is AWESOME! Thank you!Wed, 14 Jul 2010 12:36:37 GMTEric_AZhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxYou could try this forum link:[url=http://www.sqlservercentral.com/Forums/Topic892846-391-1.aspx#bm901179]http://www.sqlservercentral.com/Forums/Topic892846-391-1.aspx#bm901179[/url]RegardsSun, 11 Jul 2010 12:22:55 GMTanthonyhhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxHi Great effort in getting out this procedure but what about decimal(p,s) that doesn't seem to reflect either.Sun, 11 Jul 2010 11:00:56 GMTkrishnavamshi24http://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxThe other thing that is missing is any code to handle decimal and numeric datatypes with Precision and Scale.Sat, 10 Jul 2010 14:07:37 GMTAndy Reillyhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxShould look into using n-hibernate tool such as CSLA, LLBLGen Pro, etc... The CRUD procedures should reside in the DataAccessLayer.dll.Fri, 09 Jul 2010 11:10:05 GMTJames Tranhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxI understand why people write these utilities, but Microsoft changes system tables and relations between objects with every release. Utilities like this can become quickly out of date, and if deployed into some sort of production automation could cause problems on an upgrade.Fri, 09 Jul 2010 10:54:14 GMTsteve.simpsonhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxI appreciate the thought and effort, but this is technology from 8+ yrs ago.. use an ORM and focus on value-added customer functionalityFri, 09 Jul 2010 08:31:26 GMTdg-453058http://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxYall should check out MyGeneration. They have a huge repository of scripts to generate anything for any language.http://www.mygenerationsoftware.com/templatelibrary/default.aspxFri, 09 Jul 2010 08:24:34 GMTmike.mahonhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxVery ingenious, but honestly, the proliferation of procs isn't a good thing. This is better handled as others have said in client code where one class can dynamically generate all the needed code at run time. This avoids all types of issues and allows you to keep all the code in the same place - business logic, data validation, etc. Still, it is very ingenious, I just think less effort would be involved in the long run by keeping such code out of the database.Fri, 09 Jul 2010 07:27:34 GMTjcraddockhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspx[quote][b]Deepthi Viswanathan Nair (7/22/2009)[/b][hr]Comments posted to this topic are about the item [B]&lt;A HREF="/articles/Stored+Procedures/67055/"&gt;Dynamic creation of Insert, Update, Delete Stored procedure&lt;/A&gt;[/B][/quote]I found an error when column type is NVARCHAR.ex. nvarchar(255) ----&gt; used your store procedure the datatype become nvarchar(510)Fri, 09 Jul 2010 04:04:49 GMTfirmwuhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxThank u.it's very very useful & time saving for the developers.Fri, 09 Jul 2010 02:14:25 GMTsukhendasshttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxI use CodeSmith templates for this task, and much more. You can find my article about [url=http://mkdot.net/blogs/boban/archive/2009/11/09/codesmith-a-tool-for-every-developer.aspx]here[/url]. In the [url=http://mkdot.net/community/cfs-file.ashx/__key/CommunityServer.Components.PostAttachments/00.00.01.04.56/CodeSmith_2D00_Boban_2D00_Templates.zip]zip [/url]file beside template for procedures, there is also template for data layer class for table and bussiness object class.Fri, 09 Jul 2010 01:17:34 GMTBoban Stojanovski-455883http://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxI use the T4 template processor provided freely in VisualSudio for this. It is much more flexible and easier to read. Also, I can point it to a whole database of tables or a set of tables and generate a whole wad of stored procs. Here is an article about it: [url]http://www.olegsych.com/2008/01/how-to-use-t4-to-generate-crud-stored-procedures [/url]Thu, 08 Jul 2010 22:32:06 GMTmidgardhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxIt fails to create the create or insert procs. The delete proc is successfully created.Tue, 23 Mar 2010 06:17:15 GMTbrosspaxedihttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxCan you post the SP which is created and giving error?Mon, 22 Mar 2010 23:42:41 GMTSAMARDEEPhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxI only modified this to use the prefix "PEC_" rather than sp_, and my table is PEC_edi_834The proc gets created with no error, but when I try to run the proc, I get:Msg 156, Level 15, State 1, Procedure PEC_pec_edi_834_insert, Line 1Incorrect syntax near the keyword 'BEGIN'.Msg 137, Level 15, State 2, Procedure PEC_pec_edi_834_insert, Line 1Must declare the scalar variable "@Payer_ID_Code".Msg 137, Level 15, State 2, Procedure PEC_pec_edi_834_update, Line 1Must declare the scalar variable "@Payer_ID_Code".Procedure [dbo].[PEC_pec_edi_834_delete] Created Successfully Any ideas?Thanks.Mon, 22 Mar 2010 11:32:24 GMTbrosspaxedihttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxHi,See <a style="text-decoration:underline;" href="http://www.sqlservercentral.com/FindForumThread/67630 ">updated script</a>for this and many other modifications..in this script..Regards.Thu, 27 Aug 2009 23:11:26 GMTSAMARDEEPhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxNeed to do replace('(' + Convert(varchar(10),syscolumns.length) +')','(-1)','(MAX)') to handle the varchar(max) datatype, wich shows as (-1) and screws the code...Thu, 27 Aug 2009 13:26:50 GMTblopezyahttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxI never think about that.. it's a nice catch.. i would incorporate this ans update the sp.. thanks for ur comment..Tue, 28 Jul 2009 22:43:52 GMTDeepthi Viswanathan Nairhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxThere's also an issue when column names contain spaces, the stored procedure parameter names will also have the space in them and causes a syntax error. Eg. Where a table has a column named 'Product Name' the following is generated:[code="sql"]CREATE PROCEDURE sp_something_insert @Product Name varchar(50)AS...[/code]I realise it's not good practice to use spaces in column names but somone will.Other odd characters are also potential hazards here.Tue, 28 Jul 2009 04:41:34 GMTnigel.http://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxI work a system with hundreds of tables and we did have two stored procedures for each that were dynamically generated using our code generator. We've now moved away from this since the code foot print was huge and simple insert, update and delete statements could be better achieved in our c# code.We have a code generator that creates a c# class (in our data layer) for evert table and then calling methods (that were dynamically generated based on table indexes and columns etc) this dynamically generates the sql and executes using sp_executesql. This change in how we execute these statements has been really well received, leaving the stored procedures for more complex tasks, reducing the vast no. of sp's we had and making it far more manageable.Nice article though :)Fri, 24 Jul 2009 01:30:58 GMTdungimonhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxNice effort, and thanks for sharing it across.Fri, 24 Jul 2009 00:51:17 GMTSudarsan Srinivasanhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxthanks for ur comment.. i would change it..regardsdeepthiThu, 23 Jul 2009 21:35:17 GMTDeepthi Viswanathan Nairhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspx[quote][b]ddunn (7/23/2009)[/b][hr]I use SQL Admin Studio as it has a customizable template engine (I used it to write Delphi code to access stored procedures). It adds the following code to the top of the generated scripts so you don't have to worry about whether the stored procedure exists or not. Note that it creates the script in an editor so you can work with it before executing it.[code]IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MyStoredProcedure]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[MyStoredProcedure]GO[/code][/quote]You may even want to try something along these lines for the script. It avoids the drop which can cause auditing and/or permission issues. This method basically lets it run every time whether it exists or not.:[code]IF OBJECT_ID(N'[dbo].[Proc_Name]') IS NULL BEGIN EXEC ('CREATE PROCEDURE [dbo].[Proc_Name] AS BEGIN SELECT ''STUB'' END');END;GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Proc_Name] ASrest of code....[/code]Also as said before the procs should NOT be named with an "sp_". Will cause a performance hit.Thu, 23 Jul 2009 15:15:42 GMTAdam Gojdashttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxThe current version of the script needs an identity column in the table.Thu, 23 Jul 2009 15:09:38 GMTMark-527630http://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxHi there,I tried to execute your stored procedure, no error, but didn't create stored procedures for Insert, update, and delete. From your first version of your stored procedure, I got an error for insert and update. The errors said 'Must declare the scalar variable "@VariableName". ' The deleted sp was created sucessfully. Any idea? thanksAKThu, 23 Jul 2009 13:29:10 GMTlexter_jj_liahttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxOrIF OBJECT_ID('dbo.SPName', 'P') IS NOT NULLBEGIN DROP PROCEDURE [dbo].[SPName]ENDThu, 23 Jul 2009 12:34:59 GMTDouglas Osborne-456728http://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxthere are places for improvement, and the code in the article was updated in an earlier comment. I have updated the article to reflect that.I think SELECT is a good thing to add. I have added 2 SELECT procs in my own generation procs before. One to return all values, and one to return a specific value. I've had the need for both those procs in many tables.Thu, 23 Jul 2009 09:01:45 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspx... or see http://www.ssmstoolspack.com/Main.aspxThu, 23 Jul 2009 08:15:46 GMTdavid.wright-948385http://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxThere is an issue with your proc. You use varchar(1000) and varchar(2000), the problem with this is with large tables this is not large enough. I changed all of those to varchar(max) and it works fine.One more thing missing is a select. You have the insert, update and delete, but select is also needed.BenThu, 23 Jul 2009 08:03:01 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxWe have a very similar set of dynamic sproc creation sprocs at my work. I find them useful in a general sense but (1) hard to look at and (2) hard to conditionally customize. I think this is a good example of something that can be implemented with greater clarity in c#. You would want similar metadata queries to get the table information, but I think applying these to a template would look tons better in app code. It could be a clr UDF if you wanted to keep it in the database, but I think the ideal solution would be to query metadata with FOR XML and then pass that XML into a standalone app. I definitely have servers that I'm prohibited from enabling CLR on, but I can obviously still run the FOR XML query.Thu, 23 Jul 2009 07:35:02 GMTAdrian Hainshttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxtruth, it does not work well with any table!this is the message:Procedure [dbo].[sp_iv00103_insert] Created Successfully Msg 8102, Level 16, State 1, Procedure sp_et_create_sps_for_table, Line 189Cannot update identity column 'DEX_ROW_ID'.Procedure [dbo].[sp_iv00103_delete] Created Successfully thanks!Thu, 23 Jul 2009 07:21:26 GMTmegasoft2http://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxNice article.Wouldn't you rather use MERGE instead?Thu, 23 Jul 2009 07:07:48 GMTadi - doccolabs.comhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxI use SQL Admin Studio as it has a customizable template engine (I used it to write Delphi code to access stored procedures). It adds the following code to the top of the generated scripts so you don't have to worry about whether the stored procedure exists or not. Note that it creates the script in an editor so you can work with it before executing it.[code]IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MyStoredProcedure]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[MyStoredProcedure]GO[/code]Thu, 23 Jul 2009 06:49:59 GMTddunn-1115368http://www.sqlservercentral.com/Forums/Topic757979-444-1.aspx[quote][b]dbaker (7/23/2009)[/b][hr]would be better NOT to use the sp_ prefix ![/quote]Strongly agreed. If you do not intend to put a stored procedure in the master database (which should not be done unless you have a good reason for doing so and know the consequences) then do not use the "sp_" prefix. On the rare occasion when you want to place a stored procedure in the master database (usually your home-grown administrative procedures) then you can use "sp__" with two underscores to avoid colliding with current or future Microsoft stored procedures.Thu, 23 Jul 2009 06:03:54 GMTNoel McKinneyhttp://www.sqlservercentral.com/Forums/Topic757979-444-1.aspxwould be better NOT to use the sp_ prefix !this is clear in BOL[font="Courier New"]We strongly recommend that you not use the prefix sp_ in the procedure name. This prefix is used by SQL Server to designate system stored procedures.We recommend that you do not create any stored procedures using sp_ as a prefix. SQL Server uses the sp_ prefix to designate system stored procedures. The name you choose may conflict with some future system procedure. If your application uses nonschema qualified name references and your own procedure name conflicts with a system procedure name, your application will break because the name binds to the system procedure, not your own.[/font]regards,DickThu, 23 Jul 2009 05:38:22 GMTdbaker-620086