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 12:26 PM
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
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]
Post #1062242
Posted Thursday, February 10, 2011 5:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, February 15, 2011 9:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, January 1, 2013 5:15 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 3, 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
Posted Tuesday, January 1, 2013 12:50 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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
Posted Tuesday, January 1, 2013 2:00 PM
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
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
Posted Tuesday, January 1, 2013 2:05 PM
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
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
Posted Wednesday, January 2, 2013 8:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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
Posted Wednesday, January 2, 2013 9:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 5, 2014 11:10 PM
Points: 16, Visits: 87
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 »

Add to briefcase «««12345

Permissions Expand / Collapse