Dynamic creation of Insert, Update, Delete Stored procedure

Question

Dynamic creation of Insert, Update, Delete Stored procedure

Viewing 15 posts - 31 through 45 (of 49 total)

You must be logged in to reply to this topic. Login to reply

midgard SSC Enthusiast Points: 187 More actions · Answer 1

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

Boban Stojanovski-455883 SSC Veteran Points: 215 More actions · Answer 2

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.

sukhendass Old Hand Points: 322 More actions · Answer 3

Thank u.it's very very useful & time saving for the developers.

firmwu SSC Rookie Points: 36 More actions · Answer 4

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)

jcraddock Default port Points: 1498 More actions · Answer 5

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.

mike.mahon SSC Journeyman Points: 76 More actions · Answer 6

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

dg-453058 Newbie Points: 7 More actions · Answer 7

I appreciate the thought and effort, but this is technology from 8+ yrs ago.. use an ORM and focus on value-added customer functionality

steve.simpson SSC Rookie Points: 27 More actions · Answer 8

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.

James Tran SSC Journeyman Points: 97 More actions · Answer 9

Should look into using n-hibernate tool such as CSLA, LLBLGen Pro, etc... The CRUD procedures should reside in the DataAccessLayer.dll.

Andy Reilly Hall of Fame Points: 3677 More actions · Answer 10

The other thing that is missing is any code to handle decimal and numeric datatypes with Precision and Scale.

krishnavamshi24 Mr or Mrs. 500 Points: 553 More actions · Answer 11

Hi

Great effort in getting out this procedure but what about decimal(p,s) that doesn't seem to reflect either.

anthonyh SSC Veteran Points: 212 More actions · Answer 12

You could try this forum link:

http://www.sqlservercentral.com/Forums/Topic892846-391-1.aspx#bm901179

Regards

Eric_AZ Grasshopper Points: 18 More actions · Answer 13

Eric_AZ

Grasshopper

Points: 18

July 14, 2010 at 12:36 pm

#1193062

This is AWESOME! Thank you!

R-CHERIF Ammar SSC Journeyman Points: 87 More actions · Answer 14

to check your decimal(p,s) you can do this :

CaseWhen systypes.xusertype =106 Then '(' + Convert(varchar(10),syscolumns.xprec) +','+Convert(varchar(10),syscolumns.xscale)+')'

anthonyh SSC Veteran Points: 212 More actions · Answer 15

Eric_AZ (7/14/2010)
This is AWESOME! Thank you!

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?