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 Wednesday, February 09, 2011 10:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
Comments posted to this topic are about the item Automatically Generate Stored Procedures

James
MCM [@TheSQLPimp]
Post #1061692
Posted Wednesday, February 09, 2011 10:22 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 11, 2013 3:27 PM
Points: 127, Visits: 132
It seems that you are well famaliar in SQL, so can you tell is it always needed to have a primary key, in table and is it needed to have a identity primary key?
what happens if i have no primary key or have a primary key but a non-identity one?
your code will not work.
Still i appreciate your hard work


Rahul
Post #1061694
Posted Wednesday, February 09, 2011 11:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 13, 2011 1:57 PM
Points: 1, Visits: 34
Awesome! Thanks!
Post #1061709
Posted Thursday, February 10, 2011 12:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 5:12 AM
Points: 5, Visits: 41
how can i use it in practicle manner can u give one example
Post #1061728
Posted Thursday, February 10, 2011 12:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 13, 2014 1:08 AM
Points: 53, Visits: 247
It's only handling identity PKS and that's quite limiting for my taste. Maybe you should evolve it some more and it could be useful for more people.
Post #1061735
Posted Thursday, February 10, 2011 1:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
@Rahul The Dba
A primary key is always a good idea, as it enforces uniqueness. This doesn't have to be an identity value though and I have tables, especially ones with ISO Codes in, where the code is the primary key. I realise that my code only works on identity primary keys but to deal with all forms of tables would be a much bigger task. I chose the most common type of table so that the code was useful to the most people.

@hardikce.08
A practical example: ok, so I design a new database with 40 tables in, 30 of which are tables with a single identity primary key, I run the code and within seconds I have 90 new stored procedures which I didn't have to write manually. This both saves time and also avoids mistakes, as the column names and data types are read from the system tables and so will always be correct. Also you may have a corporate policy that say denies access to base tables and states that all data access must be through views - no problem, just change the where clauses in the code to get rid of the one primary key identity columns restriction and write a new stored procedure to create the views. You can then rerun this periodically to pick up new columns and names changes etc.

@Kristian Ask
I realise that this is quite limiting Kristian but, as I say, I had to shoot for a certain type of table in the article, so I chose the most common type (in my experience).

Folks, the article is intended to show you what can be achieved with simple automation code, it was not intended to be instantly useable on all your databases, as they are all different and we will all have different architectures. You may for instance not have sperate insert and update procedures but one "save" or "set" procedure, in which case you will need to adjust the code accordingly, plus we all have corporate standards to adhere to. The article is intended to give an example that you can use as a starting point for doing your own automation. You will never be able to cover every table design with automation code as it simply isn't possible but, if you can automate 60%, that gives you more time to concentrate on the fun stuff. The tradeoff is this: you could spend a long time coding the automation code to handle every eventuality and still never get it 100% correct so surely it is better to automate the simple and dull tasks on certain types of tables, giving you more time for the complex and interesting ones that still have to be done manually.

Cheers, James

P.S. PLEASE DO NOT RUN THIS IN ANY DATABASE THAT HAS THE SAME STORED PROCEDURE NAMING CONVENTIONS, AS YOUR EXISTING PROCEDURES WILL BE DROPPED AND RECREATED!!! ADVENTUREWORKS IS ALWAYS A SAFE PLACE TO TEST.


James
MCM [@TheSQLPimp]
Post #1061750
Posted Thursday, February 10, 2011 1:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 15, 2011 3:41 AM
Points: 2, Visits: 11
In the era of ORM where the CRUD operations are easily automated by MS entity framework and third parties like subsonic, any reason of doing this at database level. I appreciate your hard work but do you think this is now really needed?
Post #1061759
Posted Thursday, February 10, 2011 2:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
@Mahesh-457542
I for one have not used the ADO.NET Entity Framework as yet, as I do all middle-level business objects in WFC, due to the control this gives me and the wide client base it can support. I am researching more into the ADO.NET Entity Framework however I don't expect to be taking it up in the very near future. I see that it can build the data access level for me but I can also see that I'd have to invest a lot of time to add extra logic to these objects and also to implement far more triggers and extra methods to include the extra functionality I need. Thank you for the post though, as it has reminded me to look into this further again. Maybe in the future you may not do this in the database layer but for now I think many people will.

For your information, in the research I have done on ADO.NET Entity Framework and Subsonic, the ADO.NET Entity Framework benchmarks must faster.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1061763
Posted Thursday, February 10, 2011 2:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:46 AM
Points: 5, Visits: 13
hello there

your script execute successfully but,it doesnt create stored procedure ,canb you post an example.

thanks
Post #1061774
Posted Thursday, February 10, 2011 2:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:46 AM
Points: 335, Visits: 1,956
Rahul The Dba (2/9/2011)
It seems that you are well famaliar in SQL, so can you tell is it always needed to have a primary key, in table and is it needed to have a identity primary key?
what happens if i have no primary key or have a primary key but a non-identity one?
your code will not work.
Still i appreciate your hard work


You should always have a primary key on a table.
Post #1061776
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse