存储过程来创建表 [英] store procedure to create a table

问题描述

我想创建一个存储过程来创建一个表，只有在执行过程中才能给出表名和字段。有可能..

I want to create a stored procedure to create a table where the table name and the field has to be given only during executing the procedure.? Is it possible..

推荐答案

是的，你可以做到！但是你需要处理它是一件复杂的事情。在创建表时，首先需要检查表名是否存在。然后我们需要为此编写动态sql执行查询。请试试这个想法

Yes you can do it !!! But it is a complex thing you need to handle that. While Creation of table first you need to check the table name if exists or not. Then we need to write a dynamic sql execution query for that. Please give a try with this Idea

是的，你可以这样做，看下面的代码。

Yes you can do it, look at following code.

create PROCEDURE sproc_CreateTableAtRuntime
(
	@TableName NVARCHAR(128)
	,@Column1Name NVARCHAR(32)
	,@Column1DataType NVARCHAR(32)
	,@Column1Nullable NVARCHAR(32)
)
AS
Begin
	DECLARE @SQLString NVARCHAR(MAX)
	SET @SQLString = 'CREATE TABLE ' + @TableName + '( '+ @Column1Name + ' ' + 	@Column1DataType + ' '+ @Column1Nullable +')' 
	EXEC (@SQLString)
End

现在您可以按如下方式执行存储过程：

Now you can execute the store procedure as follows:

sproc_CreateTableAtRuntime 'MyTable','MyColumn1','Int','Not Null'

这是一个例子

hi , this is an Example

create table myTbl1 (name nvarchar(100) not null ,family varchar(100)
primary key (name))

您还可以添加外键，...

you can also Add Foreign Keys , ...

这篇关于存储过程来创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！