dynamic creation of table with stored procedure

Results 1 to 2 of 2

Thread: dynamic creation of table with stored procedure

  1. #1
    Anurag Guest

    Default dynamic creation of table with stored procedure

    Can somebody help me in creating stored procedure for dynamic creation of tables.<BR>I am writing this procedure:-<BR>CREATE PROCEDURE branchim <BR>@tabname varchar(50)<BR>AS<BR>create table [@tabname] <BR>(<BR> item_id varchar(4) primary key,<BR> category varchar(4),<BR> item_name varchar(20),<BR> pmu varchar(8),<BR> uom varchar(8),<BR> rate numeric(8,5),<BR> class varchar(10),<BR> opening_stock numeric(9,9),<BR> receipt_quantity numeric(5,1),<BR> current_stock numeric(5,1),<BR> rol1 numeric(5,5),<BR> rol2 numeric(5,5),<BR> as_id1 varchar(5),<BR> as_id2 varchar(5),<BR> mfg varchar(20),<BR> sp numeric(5,2)<BR>)<BR><BR>for execution :-<BR>exec branchim @tabname=&#039;sp&#039;<BR>but the problem i&#039;m facing is that the sql-server is creating table with the input parameter name ie. @tabname.<BR>I will be thankful for your response to my question.<BR>Anurag

  2. #2
    Join Date
    Dec 1969

    Default RE: dynamic creation of table with stored procedur

    You will need to something like this:<BR><BR>CREATE PROCEDURE branchim <BR>@tabname varchar(50)<BR>AS<BR><BR>Execute(&#039;create table [&#039; + @tabname + &#039;] (item_id varchar(4) primary key, category varchar(4),....&#039;)<BR><BR>What I do here is make one big string with the command you want to execute. As far as I know, this is the only way to dynamically create a table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts