Post reply

Generic insert & update Procedure

  • October 9, 2007 at 5:58 am

    #98672

    hi all,

    i want to write a generic Insert & update procedure.It should work with different tables having diffrerent datatypes.suppose let say table1 may have col1 int,col2 varchar... and let say table2 may have col1 varchar,col2 char..like that..and so on..

    generally till now wat iam doing is that iam writting different stored procedure for different tables..

    Now i want to write One Insert & update procedure in my project...and for all the tables in the project i want to use only that procedure.

    Is it Possible? If yes kindly help me ..on resolving this issue..

    Thanks & regards

    suman

  • October 9, 2007 at 6:04 am

    #740306

    please don't multiple post.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • October 9, 2007 at 6:18 am

    #740316

    hi,

    iam sorry for that...iam new to this forum..and this caused me some dilema where to post? so in such a situation i have done like that.ok..

    kindly forgive me..

    thanks & regards

    suman

  • October 10, 2007 at 10:34 am

    #740900

    I think you are going to get into a situation where you have way too many variables than are user friendly. Trying to keep track of what variable corresponds to what column, I would think, will be much more work in the long run than having case specific procedures. Just my oppinion.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • October 25, 2007 at 7:34 am

    #745365

    I would strongly advise you NOT to do this. In my experience you will end up with a big mess and lots of dependencies you don't want.

    Try to find a code generator or write your own. We have an in house code generator that reads the table schema and quickly generates the procs for a specific table.

    I believe that when your procs reference different tables based on logic in the proc, that they will recompile when you don't expect it. I don't have the exact info on this, but if you try it in profiler you will see it happen. (perphaps other will explain)

    Doug

  • October 25, 2007 at 10:14 am

    #745433

    How are you going to know which table to update, is it based on a parameter?

    CREATE PROCEDURE GenericProc

    @TableName VARCHAR(100),

    @Process VARCHAR(1)

    AS

    BEGIN

    ..

    @TableName = Table you are going to update or insert

    @Process = 'U' - Update, 'I' - Insert

    It is possible you can write a generic procedure to do it. I wrote one before but it was not easy.

  • October 26, 2007 at 9:14 am

    #745778

    If your main challenge is dynamically obtaining the structure for any table, doing something like the following may help:

    declare @sql varchar(1000), @TblNm varchar(100)

    set @TblNm = 'Table'

    set @sql = 'select * into #Table from ' + @TblNm + ' where 1 = 0 '

    + 'select * from #Table'

    exec(@sql)

    You can then do insert, updates, etc... using the temp table and drop when done.

  • November 5, 2007 at 5:31 am

    #748188

    Writing common sp for all is always complecated caz if suppose in future any changes have to be done it will affect the other ones .

    so better to maintain it seperatly. 🙂

  • November 6, 2007 at 12:29 am

    #748531

    One of the advantages of stored procedures is that they can/will use the same query plan repeatedly based on the assumption that the stored procedure will be doing pretty much the same thing every time it is called.

    Usually, overly generic procedures end up being nothing more than a wrapper for dynamic SQL (e.g. exec @string) which results in the stored procedure being no better/worse than a SQL statement/query dynamically generated by the client application.

    As previously recommended in this thread, avoid trying to create stored procedures that can do "everything" - the end result is usually a mess that performs no better or even worse than a regular SQL statement.

    Joe

  • November 6, 2007 at 7:10 am

    #748641

    As others have said. Don't do this. If you think about it, creating a completely generic procedure to insert into the database is exactly the same as just using an insert statement.

    The reason to use stored procedures is to allow for more controlled access to the database. If you are using generic routines you lose control over security because everyone who can insert at all will need permissions to this procedure, and will therefore be able to insert into any table.

    If what you want is a routine that does an update if the row exists and an insert otherwise, you can do this. You will need one routine per table and the sql would be something like:

    update

    where table. = @paramPrimaryKey

    if @@RowCount = 0

    INSERT ) values (@param1, @param2...)

    --

    JimFive

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply