SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automatically Generate Stored Procedures


Automatically Generate Stored Procedures

Author
Message
mfagan12
mfagan12
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 196
Great 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:-).
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 687
Although 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, James

James
MCM [@TheSQLPimp]
chris 24158
chris 24158
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 227
I'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.
pdnethercott
pdnethercott
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 28
Thank 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!
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 687
Chris,

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, James

James
MCM [@TheSQLPimp]
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 687
@pdnethercott
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, James

James
MCM [@TheSQLPimp]
jwbart06
jwbart06
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2501 Visits: 438
This 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.
emmchild
emmchild
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 408
good read. Have you considered a merge statement version? How about using about using user defined table types?



James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 687
Glad you both enjoyed it and, yes, having just been put onto merge, I may well start using this.

Cheers, james

James
MCM [@TheSQLPimp]
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12655 Visits: 8562
1) 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.
http://www.ssmstoolspack.com/

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search