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

Automatically Generate Stored Procedures Expand / Collapse
Author
Message
Posted Thursday, February 10, 2011 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 7:40 AM
Points: 9, Visits: 128
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.
Post #1062055
Posted Thursday, February 10, 2011 9:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
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]
Post #1062082
Posted Thursday, February 10, 2011 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 11, 2014 11:35 AM
Points: 4, Visits: 150
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.
Post #1062091
Posted Thursday, February 10, 2011 9:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 6:16 AM
Points: 44, Visits: 25
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!
Post #1062097
Posted Thursday, February 10, 2011 9:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
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]
Post #1062099
Posted Thursday, February 10, 2011 9:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
@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]
Post #1062106
Posted Thursday, February 10, 2011 9:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 12:21 PM
Points: 2,413, Visits: 418
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.
Post #1062110
Posted Thursday, February 10, 2011 9:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 7, 2014 7:51 PM
Points: 42, Visits: 351
good read. Have you considered a merge statement version? How about using about using user defined table types?


Post #1062115
Posted Thursday, February 10, 2011 9:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
Glad you both enjoyed it and, yes, having just been put onto merge, I may well start using this.

Cheers, james


James
MCM [@TheSQLPimp]
Post #1062120
Posted Thursday, February 10, 2011 10:30 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1062141
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse