Creating Stored Procs for all tables

Question

Post reply

Creating Stored Procs for all tables

bkirk

SSCrazy

Points: 2824
More actions
December 29, 2009 at 3:58 pm

#217923

I have been working on a new ecommerce database schema template and i have nearly finished setting up all my tables, PK's and FK's, etc...
However to create my Data Access Layer i was going to use Stored Procedures for all CRUD operations.
Does anyone know if a generic SP creation tool or T-SQL script i can use to set up majority of the CRUD SP's?
Or am i doomed to creating every single one by hand manually?
Just hoping there was a way to jump start the process since there are many tables.

Viewing 10 posts - 1 through 9 (of 9 total)

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

S3ssion SSChasing Mays Points: 602 More actions · Answer 1

We use CodeGen which automatically generates update/insert/delete etc SP's for each table

| If in Doubt...don't do it!! |

bkirk SSCrazy Points: 2824 More actions · Answer 2

I did a google search on this but can't seem to locate the specific application.

Do you have a website reference or a download link?

Wondering also if this is a product i need to purchase or is it a freebie?

S3ssion SSChasing Mays Points: 602 More actions · Answer 3

This is what we use, and works just fine:

http://www.codeproject.com/KB/vb/SQLVBCoder.aspx

| If in Doubt...don't do it!! |

Steve Jones - SSC Editor SSC Guru Points: 734498 More actions · Answer 4

Try this: http://www.sqlservercentral.com/scripts/T-SQL/67368/

S3ssion SSChasing Mays Points: 602 More actions · Answer 5

Steve Jones - Editor (12/29/2009)
Try this: http://www.sqlservercentral.com/scripts/T-SQL/67368/

[/size]

Thanx for that Steve... This is a great piece of code.

| If in Doubt...don't do it!! |

bkirk SSCrazy Points: 2824 More actions · Answer 6

Thanks to both of you. Great feedback and a big help.

I will definately use the SQL Generator app. It seems perfect for what i want plus it generates the .NET classes which was my next task anyway for web development.

I'll also give the script a try on my local dev and see what it returns.

S3ssion SSChasing Mays Points: 602 More actions · Answer 7

S3ssion

SSChasing Mays

Points: 602

December 29, 2009 at 5:42 pm

#1097283

Glad to help

| If in Doubt...don't do it!! |

Mike01 SSChampion Points: 11393 More actions · Answer 8

I use a free package from ssms tools pack it plugs into SSMS. I can create/modify templates before creating the CRUD procs, so they can be properly documented. It also comes with an option to generate insert statements. The url is http://www.ssmstoolspack.com

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Just an additional option (if you already have the tool) is to use templates in ERWIN to generate the default scripts for each table.

Otherwise, the suggestions listed are very good.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events