Automatically Generate Stored Procedures

  • Comments posted to this topic are about the item Automatically Generate Stored Procedures

    James
    MCM [@TheSQLPimp]

  • 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]

  • Awesome! Thanks!

  • how can i use it in practicle manner can u give one example

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

  • @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]

  • 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?

  • @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]

  • hello there

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

    thanks

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

  • 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

  • It'll only create stored procedures for tables that looks like this:

    CREATE TABLE [tablename] (

    identitycolumn INT IDENTITY(1,1)

    ...

    )

  • natedogg_0923 (2/10/2011)


    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

    I think you'll need to post the definition for the table for us to be able to help you...

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

  • 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