如何创建变量表？ [英] how to create table that it is variable ?

问题描述

如何将table定义为参数我想创建从用户创建表名的过程

how can i define table as parameter i want to create procedure that create a table name form the user

ALTER PROCEDURE   [dbo].[CreateTable]  
	 
	 
	@tabelname nvarchar(50)   
	
	
AS
BEGIN
 -- here for some reason this is not accepted to variable name table
	
  CREATE table  @tabelname 
  (
	[ID] [real] NOT NULL,
	[ProductName] [nvarchar](50)   NULL,
	[Category] [nvarchar](50)   NULL,
	[Price] [numeric](18, 0) NULL,
	[Instock] [bit] NULL,
	[Count] [int] NULL,
	[DateUpdate] [datetime2](7) NOT NULL,
	[Description] [nvarchar](50)   NULL,
	[model] [varchar](20)  NULL,
    PRIMARY KEY(ID)
 )
   
END;

推荐答案

其他人都是对的，但是，proc创建表的设计从根本上被打破。我怀疑你是在设置索引和外键，所以如果你想要一个堆，请使用XML。更好的是，设计一个系统，您可以通过创建大量表格（即名称和ID）来创建您正在创建的关系，并使用额外的ID存储您的数据以将其分组。



只是补充一下，我的意图是，如果你想了解更多，你可以回复，当我对你所遇到的问题有更多了解时，我可以了解更多细节。

The others are right, but, a design where a proc creates tables is fundamentally broken. I doubt you're setting indices and foreign keys, so if you want a heap, use XML. Better yet, design a system where you use a table to create the relationships you're creating by creating lots of tables, i.e. a name and id, and store your data with an extra id to break it in to groups.

Just to add, my intention was that if you want to know more, you can reply and I can go in to more detail when I know more about the issue you have.

使用 EXEC 声明



回答类似的问题

使用存储在SQL Server 2008中创建动态表程序 [ ^ ]

CREATE PROCEDURE sproc_CreateTableAtRuntime
           @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)

执行：

To execute:

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

由 click

这篇关于如何创建变量表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！