February 9, 2011 at 10:17 pm
Comments posted to this topic are about the item Automatically Generate Stored Procedures
James
MCM [@TheSQLPimp]
February 9, 2011 at 10:22 pm
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
[font="Comic Sans MS"]Rahul:-P[/font]
February 9, 2011 at 11:43 pm
Awesome! Thanks!
February 10, 2011 at 12:18 am
how can i use it in practicle manner can u give one example
February 10, 2011 at 12:57 am
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.
February 10, 2011 at 1:29 am
@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]
February 10, 2011 at 1:55 am
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?
February 10, 2011 at 2:13 am
@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]
February 10, 2011 at 2:50 am
hello there
your script execute successfully but,it doesnt create stored procedure ,canb you post an example.
thanks
February 10, 2011 at 2:52 am
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.
February 10, 2011 at 2:56 am
Hey hey hey
when i execute the function
Msg 537, Level 16, State 5, Procedure createInsertSP, Line 78
Invalid length parameter passed to the LEFT or SUBSTRING function.
Msg 537, Level 16, State 5, Procedure createInsertSP, Line 110
Invalid length parameter passed to the LEFT or SUBSTRING function.
this error appears...can anyone help me out...thanks i appreciate it
February 10, 2011 at 3:00 am
It'll only create stored procedures for tables that looks like this:
CREATE TABLE [tablename] (
identitycolumn INT IDENTITY(1,1)
...
)
February 10, 2011 at 3:01 am
natedogg_0923 (2/10/2011)
Hey hey heywhen i execute the function
Msg 537, Level 16, State 5, Procedure createInsertSP, Line 78
Invalid length parameter passed to the LEFT or SUBSTRING function.
Msg 537, Level 16, State 5, Procedure createInsertSP, Line 110
Invalid length parameter passed to the LEFT or SUBSTRING function.
this error appears...can anyone help me out...thanks i appreciate it
I think you'll need to post the definition for the table for us to be able to help you...
February 10, 2011 at 3:06 am
CREATE TABLE [dbo].[tblautosp](
[idprimary] [int] IDENTITY(1,1) NOT NULL,
[desc] [nchar](10) NULL,
CONSTRAINT [PK_tblautosp] PRIMARY KEY CLUSTERED
(
[idprimary] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
here it is..
February 10, 2011 at 3:07 am
The bit in the stored proc that's complaning folks is the query to retrieve the columns for the table minus the primary key.
@natedogg
What do you get if you execute the following (changing the where clause to your schema name, table name, and primary key?):
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'YOUR_SCHEMA_NAME'
and TABLE_NAME = 'YOUR_TABLE_NAME
and COLUMN_NAME <> 'YOUR_PK_COLUMN_NAME'
order by ORDINAL_POSITION
This is an error I didn't expect and surely would only happen if you created a table with just one identity pk column in. Is this the case? If so just add another column to the table and try again!
Cheers, James
James
MCM [@TheSQLPimp]
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply