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


Automatically Generate Stored Procedures


Automatically Generate Stored Procedures

Author
Message
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 686
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
vishnukandala
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 15
Very helpful.

http://www.ssmstoolspack.com Could be used for achieving similar.
Armando Prato-444805
Armando Prato-444805
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: 311
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
eliassal
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 37
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
Steven Willis
SSC Eights!
SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)

Group: General Forum Members
Points: 827 Visits: 1721
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).

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.

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

Group: General Forum Members
Points: 272 Visits: 686
Hi Eliassal. It was nearly two years ago that I wrote the article, so please forgive me for being rusty. There are embedded line feeds in the function, so if you change your query results to be returned as text then it should make far more sense!

In the time since the article was first published I have significantly improved this process; added support for deleted\audit tables, automatic procedure rebuilding via DML triggers, integration with SQL Server Data Tools, and more. I will be writing about this soon - post my MCM lab exam in ten days - and hopefully doing an hour long session on it at SQL Bits (session accepted but you never know if you will make the final cut hey).

Glad you liked the article. As I say, a much improved version will be up soon. I say versions but it may well be a stairway series, as it could easily span five articles.

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

Group: General Forum Members
Points: 272 Visits: 686
And the compare stuff, that rocks! Although SQL Server Data Tools does a damn fine job of this too and has intellisense.

James
MCM [@TheSQLPimp]
Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1655 Visits: 2384
James A Skipwith (1/1/2013)
... Glad you liked the article. ...


It was a great inspiration. I used this for a while and then made some improvements also. First I converted this to a Dot. Net executable as all the string handling needed was easier there. So if you want to keep it strictly in SQL Server then use a CLR procedure. Secondly our coding standards and me yelling about consistant object naming allowed me to take several shortcuts others might not be able to use. That is why I have not shared my version.

I look forward to reading your new series.

ATBCharles Kincaid
gsamborn
gsamborn
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 162
Hi

Thanks for the interesting article.

“rawheiser” said: “I have used "SQL making SQL" before in generating audit trail triggers.”

I am considering copying this code and doing exactly that since a couple of current projects are using VS-Lightswitch (and, indirectly, Entity Framework). In that situation, stored procedures seem to be more trouble than they are worth. Triggers are the obvious way to populate audit tables.

That might be a good idea for a follow-up article.
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