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»»

Dynamic creation of Insert, Update, Delete Stored procedure Expand / Collapse
Author
Message
Posted Thursday, July 8, 2010 10:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 3:54 PM
Points: 8, Visits: 44
I 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: http://www.olegsych.com/2008/01/how-to-use-t4-to-generate-crud-stored-procedures
Post #949718
Posted Friday, July 9, 2010 1:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:16 AM
Points: 59, Visits: 95
I use CodeSmith templates for this task, and much more. You can find my article about here. In the zip file beside template for procedures, there is also template for data layer class for table and bussiness object class.
Post #949760
Posted Friday, July 9, 2010 2:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:10 AM
Points: 242, Visits: 94
Thank u.it's very very useful & time saving for the developers.
Post #949775
Posted Friday, July 9, 2010 4:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 29, 2011 10:21 AM
Points: 16, Visits: 30
Deepthi Viswanathan Nair (7/22/2009)
Comments posted to this topic are about the item <A HREF="/articles/Stored+Procedures/67055/">Dynamic creation of Insert, Update, Delete Stored procedure</A>


I found an error when column type is NVARCHAR.
ex. nvarchar(255)
----> used your store procedure the datatype become nvarchar(510)
Post #949828
Posted Friday, July 9, 2010 7:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 10:36 AM
Points: 104, Visits: 98
Very 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.



Post #949949
Posted Friday, July 9, 2010 8:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 10, 2010 5:50 PM
Points: 2, Visits: 5
Yall should check out MyGeneration. They have a huge repository of scripts to generate anything for any language.

http://www.mygenerationsoftware.com/templatelibrary/default.aspx
Post #949981
Posted Friday, July 9, 2010 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 16, 2012 3:20 PM
Points: 1, Visits: 9
I appreciate the thought and effort, but this is technology from 8+ yrs ago.. use an ORM and focus on value-added customer functionality
Post #949985
Posted Friday, July 9, 2010 10:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 13, 2012 7:57 AM
Points: 17, Visits: 35
I 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.

Post #950107
Posted Friday, July 9, 2010 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2014 3:22 PM
Points: 2, Visits: 73
Should look into using n-hibernate tool such as CSLA, LLBLGen Pro, etc... The CRUD procedures should reside in the DataAccessLayer.dll.
Post #950117
Posted Saturday, July 10, 2010 2:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:52 AM
Points: 1,181, Visits: 336
The other thing that is missing is any code to handle decimal and numeric datatypes with Precision and Scale.
Post #950375
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse