Trying to insert data into an sql server table using a variable as the table name


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Trying to insert data into an sql server table using a variable as the table name

Hybrid View

  1. #1
    Julian Pickard Guest

    Trying to insert data into an sql server table using a variable as the table name

    Has any body got any idea how I can use a variable as a table name when
    trying to insert data into a table
    My code uses VB to pass parameters to a SQL server 7 stored procedure which
    will populate a table.
    Basically all I need to know is how do you write the syntax.

    eg.

    CREATE PROCEDURE [INSERT_DATA]
    @TABLENAME AS SYSNAME

    AS

    INSERT INTO @TABLENAME
    (NAME, ADDRESSS, DOB)

    VALUES
    (@NAME, @ADDRESS, @DOB)

    It will not recognize @TABLENAME as a valid table name.

    Is it the syntax???

    Any help at all would be great

    Thanks..




  2. #2
    Ted McNeal Guest

    Re: Trying to insert data into an sql server table using a variable as the table name


    Julian,

    You need to create your SQL statement as a literal string (varchar) and run
    it as an adhoc query or use the sp_executeSQL stored procedure. The latter
    is the suggested method.

    The query engine can reuse the always reuse your plan from a parameterized
    query when the parameter values change. It is important to know that even
    though the stored proc has a compliled execution plan the SQL that runs in
    this proc will not. This is because the statement is created at run-time.
    It will be cached in memory for reuse once it is executed for the first
    time. This is trival for a simple statement as this. But, it is an important
    consideration when developing more complex statments as such.

    I'll use your t-sql as an example

    CREATE PROCEDURE usp_InsertDataExample
    (@TableName varchar(75),
    @Name varchar(50),
    @DOB datetime,
    @Address varchar(150))

    AS

    DECLARE @SQLAdHoc varchar(1000)
    DECLARE @ReturnValue int

    SET @SQLAdHoc = 'INSERT INTO ' + @TableName + ' (Name, DOB, Address)'
    SET @SQLAdHoc = @SQLAdHoc + ' VALUES (@P1, @P2, @P3)'

    EXECUTE @ReturnValue = sp_executeSQL @SQLAdHoc, '@P1 varchar(50), @P2 datetime,
    @P3 varchar(150)', @Name, @DOB, @Address

    RETURN @ReturnValue

    Ted McNeal


    "Julian Pickard" <julian.pickard@virgin.net> wrote:
    >Has any body got any idea how I can use a variable as a table name when
    >trying to insert data into a table
    >My code uses VB to pass parameters to a SQL server 7 stored procedure which
    >will populate a table.
    >Basically all I need to know is how do you write the syntax.
    >
    >eg.
    >
    >CREATE PROCEDURE [INSERT_DATA]
    >@TABLENAME AS SYSNAME
    >
    >AS
    >
    >INSERT INTO @TABLENAME
    > (NAME, ADDRESSS, DOB)
    >
    >VALUES
    > (@NAME, @ADDRESS, @DOB)
    >
    >It will not recognize @TABLENAME as a valid table name.
    >
    >Is it the syntax???
    >
    >Any help at all would be great
    >
    >Thanks..
    >
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center