Automatically Generate Stored Procedures

Question

Automatically Generate Stored Procedures

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

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

mfagan12 Default port Points: 1414 More actions · Answer 1

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 SSCommitted Points: 1808 More actions · Answer 2

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 SSC Enthusiast Points: 144 More actions · Answer 3

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 Valued Member Points: 70 More actions · Answer 4

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 SSCommitted Points: 1808 More actions · Answer 5

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 SSCommitted Points: 1808 More actions · Answer 6

@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 Hall of Fame Points: 3291 More actions · Answer 7

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 Ten Centuries Points: 1010 More actions · Answer 8

good read. Have you considered a merge statement version? How about using about using user defined table types?

James A Skipwith SSCommitted Points: 1808 More actions · Answer 9

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 SSC Guru Points: 134017 More actions · Answer 10

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

James A Skipwith SSCommitted Points: 1808 More actions · Answer 11

Thanks Kevin, I'm definitely going to check this out!

Yeah, I've not been tempted by the ORM route yet for the very reason you mentioned.

Thanks again for the link, I know I'll be checking it out and I'm sure some other people will be too. 🙂

Appreciate the feedback.

Cheers, James

James
MCM [@TheSQLPimp]

vishnukandala Grasshopper Points: 19 More actions · Answer 12

Very helpful.

http://www.ssmstoolspack.com Could be used for achieving similar.

Armando Prato-444805 SSC Rookie Points: 48 More actions · Answer 13

Thanks for sharing... To the negative nellies, be appreciative that someone took the time

to create something that you can modify to suit your needs. If you can't use it, don't use it.

eliassal Default port Points: 1467 More actions · Answer 14

Very nice work, I agree with you on the fact we always need this type of functions even though EF and other tools exist.

I ran your functions and it works like a charm, however, the produced code is not formated, the output comes as a single line

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletemyTable]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[deletemyTable]||CREATE PROC [dbo].[deletemyTable] ( @id as int ) AS -- Author: Auto -- Created: 01 Jan 2013 -- Function: Delete a myTable table record -- Modifications: begin transaction begin try -- delete delete [dbo].[myTable] where id = @id commit transaction end try ............end catch;

Do you get the samed thing?

Thanks again and regards

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 15

eliassal (1/1/2013)
...however, the produced code is not formated, the output comes as a single line...

I couldn't function without my RedGate tools (SQL Developer Bundle[/url]).

With RedGate SQL Prompt you just highlight that long string of code, hit CTRL-K-Y, and it's automatically formatted using the formatting standards you set up. Of all the RedGate tools, that one single function is the one I use the most--dozens of times per day. The other RedGate tools are useful too, but SQL Prompt is the one I'm addicted to.