Automatically Generate Stored Procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by James Skipwith » Automatically Generate Stored Procedures

49 posts, Page 5 of 5

«««1 2 3 4 5

Automatically Generate Stored Procedures

Rate Topic

Display Mode

Topic Options

Author

Message

James A Skipwith

Posted Thursday, February 10, 2011 12:26 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:20 AM
Points: 59, Visits: 276

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

Appreciate the feedback.

Cheers, James

James
MCM [@TheSQLPimp]

Post #1062242

vishnukandala

Posted Thursday, February 10, 2011 5:05 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 14, 2012 6:14 AM
Points: 1, Visits: 15

Very helpful.

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

Post #1062371

Armando Prato-444805

Posted Tuesday, February 15, 2011 9:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 12, 2012 7:38 AM
Points: 44, 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.

Post #1064347

eliassal

Posted Tuesday, January 01, 2013 5:15 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 03, 2013 12:52 PM
Points: 519, Visits: 26

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

Post #1401578

Steven Willis

Posted Tuesday, January 01, 2013 12:50 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 284, Visits: 1,248

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.

Post #1401619

James A Skipwith

Posted Tuesday, January 01, 2013 2:00 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:20 AM
Points: 59, Visits: 276

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]

Post #1401624

James A Skipwith

Posted Tuesday, January 01, 2013 2:05 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:20 AM
Points: 59, Visits: 276

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]

Post #1401625

Charles Kincaid

Posted Wednesday, January 02, 2013 8:37 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825

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.

ATB

Charles Kincaid

Post #1401875

gsamborn

Posted Wednesday, January 02, 2013 9:32 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:28 PM
Points: 9, Visits: 62

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.

Post #1402163

« Prev Topic | Next Topic »

49 posts, Page 5 of 5

«««1 2 3 4 5

Permissions