Click here to monitor SSC
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-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 652
Comments posted to this topic are about the item Automatically Generate Stored Procedures

James
MCM [@TheSQLPimp]
Rahul The Dba
Rahul The Dba
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 133
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:-P
ASPete
ASPete
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 34
Awesome! Thanks!
hardikce.08
hardikce.08
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 46
how can i use it in practicle manner can u give one example
Kristian Ask
Kristian Ask
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 251
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.
James A Skipwith
James A Skipwith
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 652
@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]
Mahesh-457542
Mahesh-457542
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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?
James A Skipwith
James A Skipwith
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 652
@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]
natedogg_0923
natedogg_0923
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 13
hello there

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

thanks
Samuel Vella
Samuel Vella
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 2141
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.
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