How to Dynamically Create Tables in T-SQL Stored Procedures?

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

Creating a table variable as a temporary table, as shown in the provided example, is incorrect. To create a temporary table, use the following syntax:

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

To declare a table variable, use this syntax:

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

Dynamic SQL offers an alternative method for creating tables in stored procedures. You can construct a SQL statement as a string and execute 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 as follows:

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

However, creating tables dynamically in stored procedures is generally discouraged due to:

• Complexity: Handling complex table structures can be challenging.
• Scalability: All tables end up on the primary file group, potentially causing I/O contention.
• Planning: Strategic placement of tables on filegroups for better performance is crucial.

If you require tables to be created dynamically, consider exploring other solutions such as having shops map to existing product tables with defined prices.

The above is the detailed content of How to Dynamically Create Tables in T-SQL Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!