none
How to create dynamic Insert Query Stored Procedure

    Question

  • Hello Everyone,

    I want to create Insert Stored Procedure which will store data in table.

    now This Stored procedure should be dynamic so that for Inserting into differnt table i dont have make stored procedure for every insert.

    is it possible using SQL query Kindly suggest or provide some link which are helpful for me

     

    Friday, December 16, 2011 11:20 AM

Answers

  • It's possible. It also a very bad idea. I would even say that it's completely useless.

    There are two ways to go. One is that you construct all SQL in the client and skip using stored procedures altogether.

    Or you use stored procedures, which means that you write one stored procedure for table you need to insert in. Keep in mind that stored procedures often implement business rules, and these business rules are of course unique to each stored procedure.

    For a deeper discussion on this topic, I have an extensive article on dynamic SQL on my web site:
    http://www.sommarskog.se/dynamic_sql.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 16, 2011 11:30 AM
  • now This Stored procedure should be dynamic so that for Inserting into differnt table i dont have make stored procedure for every insert.


    You should make a stored procedure for each table INSERT. That is production strength.

    INSERT stored procedure example:

    http://www.sqlinfo.net/sqlserver/sql_server_stored_procedure_INSERT.php

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    Friday, December 16, 2011 1:50 PM
  • I completely agree with Erland. Writing one generic INSERT stored procedure is a very bad idea.

    On the other hand, if you want to generate common stored procedures for basic INSERT/UPDATE/DELETE operations based on some template, then this is quite easy. I do remember couple of years ago I used MyGeneration which is a free tool and it can do much more than just generate stored procedures.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, December 16, 2011 3:09 PM

All replies

  • create procedure TestP ( @table sysname )
    as
    begin
    declare @str varchar(max)
    set @str = 'insert into ' + @table + ' ... '
    exec (@str)
    end
    


    http://www.t-sql.ru
    Friday, December 16, 2011 11:23 AM
  • It's possible. It also a very bad idea. I would even say that it's completely useless.

    There are two ways to go. One is that you construct all SQL in the client and skip using stored procedures altogether.

    Or you use stored procedures, which means that you write one stored procedure for table you need to insert in. Keep in mind that stored procedures often implement business rules, and these business rules are of course unique to each stored procedure.

    For a deeper discussion on this topic, I have an extensive article on dynamic SQL on my web site:
    http://www.sommarskog.se/dynamic_sql.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 16, 2011 11:30 AM
  • Can you pls tell me how to use it I am not getting this Stored procedure properly
    Friday, December 16, 2011 11:31 AM
  • You can see this working example:
    But as suggested by Erland try to avoid this


    CREATE TABLE MSDNUser
    (Name VARCHAR(100))

    ALTER PROCEDURE spInsertDataDyn
    (
          @TName VARCHAR(100),
          @Name  VARCHAR(100)
    )
    AS
    BEGIN
          EXEC('INSERT INTO ' +@TName+' VALUES('''+@Name+''')')
    END

    EXEC
    spInsertDataDyn 'MSDNUser','Jim'

    Shatrughna.
    Friday, December 16, 2011 11:54 AM
  • now This Stored procedure should be dynamic so that for Inserting into differnt table i dont have make stored procedure for every insert.


    You should make a stored procedure for each table INSERT. That is production strength.

    INSERT stored procedure example:

    http://www.sqlinfo.net/sqlserver/sql_server_stored_procedure_INSERT.php

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    Friday, December 16, 2011 1:50 PM
  • I completely agree with Erland. Writing one generic INSERT stored procedure is a very bad idea.

    On the other hand, if you want to generate common stored procedures for basic INSERT/UPDATE/DELETE operations based on some template, then this is quite easy. I do remember couple of years ago I used MyGeneration which is a free tool and it can do much more than just generate stored procedures.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, December 16, 2011 3:09 PM