Automatically Generate Stored Procedures

  • 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:-).

  • 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

    MCM [@TheSQLPimp]

  • I've done this myself, but within a 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.

  • 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!

  • 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

    MCM [@TheSQLPimp]

  • @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

    MCM [@TheSQLPimp]

  • 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.

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

  • Glad you both enjoyed it and, yes, having just been put onto merge, I may well start using this.

    Cheers, james

    MCM [@TheSQLPimp]

  • 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.

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

  • 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

    MCM [@TheSQLPimp]

  • Very helpful. Could be used for achieving similar.

  • 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.

  • 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

  • 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.


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

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