none
How to create a stored procedure that will create a table

    Question

  • Very new to SQl (started today) I have had a short intro (1page) to stored procedures without seeing any example.

    I have to create a stored procedure that will create a table.

    I know how to create a table and I know how to create a stored procedure but I do not have any Idea How to create a procedure that would create a table.

    I have looked elsewhere online but all similar questions are way to complicated as to what I have learned.

    this is what i know :

    CREATE TABLE Customers
    (
      ID INT,
      FirstName VARCHAR(15)
      Surname VARCHAR(20)
      [Street Address] VARCHAR950)
      City VARCHAR(12)
    )
    
    
    
    
    CREATE PROCEDURE DeleteCustomer
    @IDNumber INT
    AS
    DELETE
    FROM Customers
    WHERE ID = @IDNumber
    
    
    So How would I create a procedure that creates a table?
    Do I
    CREATE PROCEDURE MakeTable
    @Customers TABLE
    AS
    TABLE
    

    I will just stop because have no clue.

    Can somebody show me code or a link where I could find this please

    Sunday, July 17, 2011 4:54 AM

Answers

  • Usually, the job to create database is not done through stored procedures. You start with creating your database and keeping the scripts you used and the stored procedures are needed to modify data in your tables.

    However, if you really need to have an SP creating a table, you can do

    Create procedure MakeCustomersTable
    AS
    
    IF OBJECT_ID('Customers','U') IS NULL
    
    CREATE TABLE Customers
    (
     ID INT,
     FirstName VARCHAR(15)
     Surname VARCHAR(20)
     [Street Address] VARCHAR950)
     City VARCHAR(12)
    )
    
    GO
    
    The check for ID is needed to prevent errors when the table is already created.


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


    My blog
    • Proposed as answer by Jason A Long Sunday, July 17, 2011 5:59 AM
    • Marked as answer by KJian_ Tuesday, July 26, 2011 2:38 AM
    Sunday, July 17, 2011 5:16 AM
  • > I have to create a stored procedure that will create a table.

    Normally, you don't create tables in stored procedures. The point with a stored procedure is to run queries to update or retrieve data, over and over again. But a table you create once, and that's that.

    The one exception is temp tables, which you often create in a procedure to have a work area. But permanent tables are normally created in deployment scripts.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Tuesday, July 26, 2011 2:38 AM
    Sunday, July 17, 2011 10:14 AM

All replies

  • Very new to SQl (started today) I have had a short intro (1page) to stored procedures without seeing any example.

    I have to create a stored procedure that will create a table.

    I know how to create a table and I know how to create a stored procedure but I do not have any Idea How to create a procedure that would create a table.

    I have looked elsewhere online but all similar questions are way to complicated as to what I have learned.

    this is what i know :

     

    CREATE TABLE Customers
    (
     ID INT,
     FirstName VARCHAR(15)
     Surname VARCHAR(20)
     [Street Address] VARCHAR950)
     City VARCHAR(12)
    )
    
    
    
    
    CREATE PROCEDURE DeleteCustomer
    @IDNumber INT
    AS
    DELETE
    FROM Customers
    WHERE ID = @IDNumber
    
    
    So How would I create a procedure that creates a table?
    Do I
    CREATE PROCEDURE MakeTable
    @Customers TABLE
    AS
    TABLE
    

     

    I will just stop because have no clue.

    Can somebody show me code or a link where I could find this please

    I tried this and it seems to work
    CREATE PROCEDURE Task1
    
    AS
    CREATE TABLE Products
    (
     ProductID INT,
     CategoryID INT,
     Name VARCHAR(30),
     Price SMALLMONEY,
     [Quatity In Stock] INT,
     PRIMARY KEY (ProductID)
    )
    

    Thanks
    Sunday, July 17, 2011 5:14 AM
  • Usually, the job to create database is not done through stored procedures. You start with creating your database and keeping the scripts you used and the stored procedures are needed to modify data in your tables.

    However, if you really need to have an SP creating a table, you can do

    Create procedure MakeCustomersTable
    AS
    
    IF OBJECT_ID('Customers','U') IS NULL
    
    CREATE TABLE Customers
    (
     ID INT,
     FirstName VARCHAR(15)
     Surname VARCHAR(20)
     [Street Address] VARCHAR950)
     City VARCHAR(12)
    )
    
    GO
    
    The check for ID is needed to prevent errors when the table is already created.


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


    My blog
    • Proposed as answer by Jason A Long Sunday, July 17, 2011 5:59 AM
    • Marked as answer by KJian_ Tuesday, July 26, 2011 2:38 AM
    Sunday, July 17, 2011 5:16 AM
  • > I have to create a stored procedure that will create a table.

    Normally, you don't create tables in stored procedures. The point with a stored procedure is to run queries to update or retrieve data, over and over again. But a table you create once, and that's that.

    The one exception is temp tables, which you often create in a procedure to have a work area. But permanent tables are normally created in deployment scripts.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Tuesday, July 26, 2011 2:38 AM
    Sunday, July 17, 2011 10:14 AM
  • Hi,

    As Earland said, tables that are static (you will need all the time) created once. So, they don't need to be created by Stored Procedure. Just do it using GUI or single query.

    In spite of this, you may need a temporary table that may be necessary to hold temporary data in order to process business need. In this case, you can use #table or table variable (@table). For #table you should create and drop it in same stored procedure. For table variable, it just act as a variable.

      -- Hash Table
    	create table #testTable
    	(
    	  ID	int,
    	  Name varchar
    	)
    	
    	--- Necessary Stuffs Here..
    	
    	drop table #testTable
    
    -- Table Variable
    declare @val table
    ( 
    	id int,
    	name varchar
    )
    


    Regards.

     

    Sunday, July 17, 2011 10:44 AM