T-SQL How to Create Tables Dynamically in Stored Procedures

T-SQL How to create tables dynamically in stored procedures?

You are using a table variable i.e. you should declare the table. This is not a temporary table.

You create a temp table like so:

CREATE TABLE #customer
(
     Name varchar(32) not null
)

You declare a table variable like so:

DECLARE @Customer TABLE
(
      Name varchar(32) not null
)

Notice that a temp table is declared using # and a table variable is declared using a @.
Go read about the difference between table variables and temp tables.

UPDATE:

Based on your comment below you are actually trying to create tables in a stored procedure. For this you would need to use dynamic SQL. Basically dynamic SQL allows you to construct a SQL Statement in the form of a string and then execute it. This is the ONLY way you will be able to create a table in a stored procedure. I am going to show you how and then discuss why this is not generally a good idea.

Now for a simple example (I have not tested this code but it should give you a good indication of how to do it):

CREATE PROCEDURE sproc_BuildTable 
    @TableName NVARCHAR(128)
   ,@Column1Name NVARCHAR(32)
   ,@Column1DataType NVARCHAR(32)
   ,@Column1Nullable NVARCHAR(32)
AS

   DECLARE @SQLString NVARCHAR(MAX)
   SET @SQString = 'CREATE TABLE '+@TableName + '( '+@Column1Name+' '+@Column1DataType +' '+@Column1Nullable +') ON PRIMARY '

   EXEC (@SQLString)
   GO

This stored procedure can be executed like this:

sproc_BuildTable 'Customers','CustomerName','VARCHAR(32)','NOT NULL'

There are some major problems with this type of stored procedure.

Its going to be difficult to cater for complex tables. Imagine the following table structure:

CREATE TABLE [dbo].[Customers] (
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [nvarchar](64) NOT NULL,
    [CustomerSUrname] [nvarchar](64) NOT NULL,
    [CustomerDateOfBirth] [datetime] NOT NULL,
    [CustomerApprovedDiscount] [decimal](3, 2) NOT NULL,
    [CustomerActive] [bit] NOT NULL,
    CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
    (
        [CustomerID] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,      ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [DF_Customers_CustomerApprovedDiscount] DEFAULT ((0.00)) FOR [CustomerApprovedDiscount]
GO 

This table is a little more complex than the first example, but not a lot. The stored procedure will be much, much more complex to deal with. So while this approach might work for small tables it is quickly going to be unmanageable.

Creating tables require planning. When you create tables they should be placed strategically on different filegroups. This is to ensure that you don't cause disk I/O contention. How will you address scalability if everything is created on the primary file group?

Could you clarify why you need tables to be created dynamically?

UPDATE 2:

Delayed update due to workload. I read your comment about needing to create a table for each shop and I think you should look at doing it like the example I am about to give you.

In this example I make the following assumptions:

1. It's an e-commerce site that has many shops
2. A shop can have many items (goods) to sell.
3. A particular item (good) can be sold at many shops
4. A shop will charge different prices for different items (goods)
5. All prices are in $ (USD)

Let say this e-commerce site sells gaming consoles (i.e. Wii, PS3, XBOX360).

Looking at my assumptions I see a classical many-to-many relationship. A shop can sell many items (goods) and items (goods) can be sold at many shops. Let's break this down into tables.

First I would need a shop table to store all the information about the shop.

A simple shop table might look like this:

CREATE TABLE [dbo].[Shop](
    [ShopID] [int] IDENTITY(1,1) NOT NULL,
    [ShopName] [nvarchar](128) NOT NULL,
    CONSTRAINT [PK_Shop] PRIMARY KEY CLUSTERED 
    (
      [ShopID] ASC
    ) WITH (
              PAD_INDEX  = OFF
              , STATISTICS_NORECOMPUTE  = OFF
              , IGNORE_DUP_KEY = OFF
              , ALLOW_ROW_LOCKS  = ON
              , ALLOW_PAGE_LOCKS  = ON
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

Let's insert three shops into the database to use during our example. The following code will insert three shops:

INSERT INTO Shop
SELECT 'American Games R US'
UNION
SELECT 'Europe Gaming Experience'
UNION
SELECT 'Asian Games Emporium'

If you execute a SELECT * FROM Shop you will probably see the following:

ShopID  ShopName
1           American Games R US
2           Asian Games Emporium
3           Europe Gaming Experience

Right, so now let's move onto the Items (goods) table. Since the items/goods are products of various companies I am going to call the table product. You can execute the following code to create a simple Product table.

CREATE TABLE [dbo].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [ProductDescription] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
 (
     [ProductID] ASC
 )WITH (PAD_INDEX  = OFF
        , STATISTICS_NORECOMPUTE  = OFF
        , IGNORE_DUP_KEY = OFF
        ,     ALLOW_ROW_LOCKS  = ON
         , ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]

GO

Let's populate the products table with some products. Execute the following code to insert some products:

INSERT INTO Product
SELECT 'Wii'
UNION 
SELECT 'PS3'
UNION 
SELECT 'XBOX360'

If you execute SELECT * FROM Product you will probably see the following:

ProductID   ProductDescription
1           PS3
2           Wii
3           XBOX360

OK, at this point you have both product and shop information. So how do you bring them together? Well we know we can identify the shop by its ShopID primary key column and we know we can identify a product by its ProductID primary key column. Also, since each shop has a different price for each product we need to store the price the shop charges for the product.

So we have a table that maps the Shop to the product. We will call this table ShopProduct. A simple version of this table might look like this:

CREATE TABLE [dbo].[ShopProduct](
[ShopID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Price] [money] NOT NULL,
CONSTRAINT [PK_ShopProduct] PRIMARY KEY CLUSTERED 
 (
     [ShopID] ASC,
      [ProductID] ASC
 )WITH (PAD_INDEX  = OFF,
     STATISTICS_NORECOMPUTE  = OFF, 
     IGNORE_DUP_KEY = OFF, 
     ALLOW_ROW_LOCKS  = ON,
     ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]

 GO

So let's assume the American Games R Us shop only sells American consoles, the Europe Gaming Experience sells all consoles and the Asian Games Emporium sells only Asian consoles. We would need to map the primary keys from the shop and product tables into the ShopProduct table.

Here is how we are going to do the mapping. In my example the American Games R Us has a ShopID value of 1 (this is the primary key value) and I can see that the XBOX360 has a value of 3 and the shop has listed the XBOX360 for $159.99

By executing the following code you would complete the mapping:

INSERT INTO ShopProduct VALUES(1,3,159.99)

Now we want to add all product to the Europe Gaming Experience shop. In this example we know that the Europe Gaming Experience shop has a ShopID of 3 and since it sells all consoles we will need to insert the ProductID 1, 2 and 3 into the mapping table. Let's assume the prices for the consoles (products) at the Europe Gaming Experience shop are as follows: 1- The PS3 sells for $259.99 , 2- The Wii sells for $159.99 , 3- The XBOX360 sells for $199.99.

To get this mapping done you would need to execute the following code:

INSERT INTO ShopProduct VALUES(3,2,159.99) --This will insert the WII console into the mapping table for the Europe Gaming Experience Shop with a price of 159.99
INSERT INTO ShopProduct VALUES(3,1,259.99) --This will insert the PS3 console into the mapping table for the Europe Gaming Experience Shop with a price of 259.99
INSERT INTO ShopProduct VALUES(3,3,199.99) --This will insert the XBOX360 console into the mapping table for the Europe Gaming Experience Shop with a price of 199.99

At this point you have mapped two shops and their products into the mapping table. OK, so now how do I bring this all together to show a user browsing the website? Let's say you want to show all the product for the European Gaming Experience to a user on a web page – you would need to execute the following query:

SELECT      Shop.*
        , ShopProduct.*
        , Product.*
FROM         Shop 
INNER JOIN  ShopProduct ON Shop.ShopID = ShopProduct.ShopID 
INNER JOIN  Product ON ShopProduct.ProductID = Product.ProductID
WHERE       Shop.ShopID=3

You will probably see the following results:

ShopID     ShopName                 ShopID  ProductID   Price   ProductID   ProductDescription
3          Europe Gaming Experience   3         1       259.99  1           PS3
3          Europe Gaming Experience   3         2       159.99  2           Wii
3          Europe Gaming Experience   3         3       199.99  3           XBOX360

Now for one last example, let's assume that your website has a feature which finds the cheapest price for a console. A user asks to find the cheapest prices for XBOX360.

You can execute the following query:

 SELECT     Shop.*
        , ShopProduct.*
        , Product.*
 FROM         Shop 
 INNER JOIN  ShopProduct ON Shop.ShopID = ShopProduct.ShopID 
 INNER JOIN  Product ON ShopProduct.ProductID = Product.ProductID
 WHERE      Product.ProductID =3  -- You can also use Product.ProductDescription = 'XBOX360'
 ORDER BY    Price ASC

This query will return a list of all shops which sells the XBOX360 with the cheapest shop first and so on.

You will notice that I have not added the Asian Games shop. As an exercise, add the Asian games shop to the mapping table with the following products:
the Asian Games Emporium sells the Wii games console for $99.99 and the PS3 console for $159.99. If you work through this example you should now understand how to model a many-to-many relationship.

I hope this helps you in your travels with database design.

Create a temporary table dynamically from calling an stored procedure

My ultimate goal is to compare columns data type of a 2nd resultset
from an SP with my expected table schema using tSQLt test case.

Refactoring the code returning a second resultset into its own proc would make this more easy to test but it is do-able.

Supposing your procedure under test looks like

CREATE PROCEDURE dbo.ProcedureUnderTest
AS
BEGIN

SELECT 1 AS ResultSet1Col1

SELECT 2 AS ResultSet2Col1, 'Foo' AS ResultSet2Col2

END

You can achieve your desired goal of validating the format of the second result set by nesting a call to tSQLt.ResultSetFilter inside an execution of tSQLt.AssertResultSetsHaveSameMetaData

CREATE TABLE #expected
(
   ResultSet2Col1 INT NULL,
   ResultSet2Col2 VARCHAR(3) NULL
)


EXEC tSQLt.AssertResultSetsHaveSameMetaData
  @expectedCommand = 'SELECT * FROM #expected',
  @actualCommand = 'EXEC tSQLt.ResultSetFilter 2, ''EXEC dbo.ProcedureUnderTest'';' 

Using dynamic sql in a procedure to create schemas and tables

ok, fixed all the misspellings, incorrect syntax and non-unique names. all better.
added a bit of error handling. hope it gets you going again.

USE master
GO
if not exists (select 1 from master.sys.databases where name = 'CTUDB')
CREATE DATABASE CTUDB
GO
USE CTUDB
GO
if object_id('AddCampus_proc','P') is not null
    begin
        drop proc AddCampus_proc;
    end;
GO
CREATE PROCEDURE AddCampus_proc(@campus varchar(50)
)
AS
BEGIN
DECLARE @DynamicSQL varchar(MAX)
SET @DynamicSQL = 'CREATE schema '+quotename(@campus)+''
begin try
    EXEC (@DynamicSQL);
end try
begin catch
    if error_number() <> 2759 --this just means the schema already exists.
        begin
            print(error_number())
            print(error_message())
        end
end catch

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Student_tbl(
StudentID numeric(4,0) not null PRIMARY KEY,
Name varchar(50) not null,
Surname varchar(50) not null,
ID_numeric numeric(13,0) not null,
Address varchar(100) not null,
CONSTRAINT '+@campus+'_Student_tbl_CheckStudentID check (len(StudentID) = 4),
CONSTRAINT '+@campus+'_Student_tbl_CheckIDnumeric check (len(ID_numeric) = 13)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Course_tbl(
CourseID integer not null PRIMARY KEY,
CourseName varchar(50) not null,
Description varchar(100) not null,
StudentID numeric(4,0) not null,
FOREIGN KEY (StudentID) REFERENCES ' + quotename(@campus) + '.Student_tbl(StudentID),
CONSTRAINT '+@campus+'_Course_tbl_CheckStudentID check (len(StudentID) = 4)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.ClassMarks_tbl(
ClassMarksID integer not null PRIMARY KEY,
StudentID numeric(4,0) not null,
CourseID integer not null,
Semester1_Mark1 integer not null check (Semester1_Mark1 between 0 and 100),
Semester1_Mark2 integer not null check (Semester1_Mark2 between 0 and 100),
Semester1_Mark3 integer not null check (Semester1_Mark3 between 0 and 100),
Semester1_Average integer not null check (Semester1_Average between 0 and 100),
Semester1_Test_Mark integer not null check (Semester1_Test_Mark between 0 and 100),
Semester2_Mark1 integer not null check (Semester2_Mark1 between 0 and 100),
Semester2_Mark2 integer not null check (Semester2_Mark2 between 0 and 100),
Semester2_Mark3 integer not null check (Semester2_Mark3 between 0 and 100),
Semester2_Average integer not null check (Semester2_Average between 0 and 100),
Semester2_Test_Mark integer not null check (Semester2_Test_Mark between 0 and 100),
FOREIGN KEY (StudentID) REFERENCES ' + quotename(@campus) + '.Student_tbl(StudentID),
FOREIGN KEY (CourseID) REFERENCES ' + quotename(@campus) + '.Course_tbl(CourseID),
CONSTRAINT '+@campus+'_ClassMarks_tbl_CheckStudentID check (len(StudentID) = 4)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Facilitator_tbl(
FacilitatorID integer not null PRIMARY KEY,
Name varchar(50) not null,
Surname varchar(50) not null,
Address varchar(100) not null,
Paycheck decimal(19,4) not null,
CourseID integer not null,
FOREIGN KEY (CourseID) REFERENCES ' + quotename(@campus) + '.Course_tbl(CourseID)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Parents_tbl(
ParentID integer not null PRIMARY KEY,
Name varchar(50) not null,
Surname varchar(50) not null,
ID_numeric numeric(13,0) not null,
StudentID numeric(4,0) not null,
FOREIGN KEY (StudentID) REFERENCES ' + quotename(@campus) + '.Student_tbl(StudentID),
CONSTRAINT '+@campus+'_Parents_tbl_StudentID check (len(StudentID) = 4)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);
END
GO
EXEC AddCampus_proc 'Nelspruit'
EXEC AddCampus_proc 'Roodepoort'
EXEC AddCampus_proc 'Sandton'
EXEC AddCampus_proc 'Boksburg'
EXEC AddCampus_proc 'Pretoria'
EXEC AddCampus_proc 'Cape_Town'
EXEC AddCampus_proc 'Vereniging'
EXEC AddCampus_proc 'Bloemfontein'
EXEC AddCampus_proc 'Polokwane'
EXEC AddCampus_proc 'Durban'
EXEC AddCampus_proc 'Stellenbosch'
EXEC AddCampus_proc 'Port_Elizabeth'
EXEC AddCampus_proc 'Pochefstroom'
EXEC AddCampus_proc 'Auckland_Park'

Creating a table using dynamic sql

Try it like this:

EXEC(@sSQL)

This is a very common error. Without the parenthesis, EXEC @sSQL means "execute a stored procedure whose name is in the @sSQL variable", rather than what you want which is probably "Execute the command string in the variable @sSQL."

How to give table name dynamically in stored procedure in SQL Server 2012

Just use dynamic SQL. Example:

CREATE PROC spGetAll (
    @tableName SYSNAME)
AS
BEGIN
    DECLARE @sql nvarchar(max) 

    IF EXISTS (SELECT name FROM sys.objects WHERE name = @tableName)
    SET @sql = 'SELECT * FROM test.'+ QUOTENAME(@tableName)
    EXEC(@sql) -- Or EXEC sp_executesql @sql
END

Stored procedure to create view with dynamic ID based on another table

Sounds to me like Row_Number() is what you are looking for. Try using this to create your view.

CREATE view myView AS
SELECT Row_Number() OVER(ORDER BY ID) ID, Name
FROM dbo.table_1

Is a cursor or second stored procedure required for a set of dynamic SQL statements with dynamic table names

You could include identity column in your temp table for the alternative way of CURSOR. modified little based on your query as below:

create table #mytablename (app_name varchar(255), count_loop int identity(1,1))
insert into #mytablename
select s.name from sysobjects s where s.type='U'

declare @max int, @current int
declare @my_app nvarchar(255)
declare @sql nvarchar(max)

set @current=1
select @max = MAX(count_loop) from #mytablename 

while @current < @max 
begin
    
    select @my_app = app_name from #mytablename where count_loop = @current

    select @sql = 'select trim(substring(left(app_name,len(app_name)-7),10,100)) from ' + @my_app
    exec(@sql)

    set @current=@current+1

end

---

Related Topics