CREATE PROCEDURE create_table
@tab_name varchar(20)--定义参数
AS
--declare @tablename varchar(10)
declare @i int --定义变量
set @i=1 --变量赋值
while @i<10
begin
--create table ss(id varchar(10) not null,name varchar(20),address varchar(20));
exec('create table '+@tab_name+@i+'(
id nvarchar(10) not null,
name nvarchar(20),
address nvarchar(20)
)')
set @i = @i+1
END
GO
--执行存储过程
exec create_table 'table_transflow_'
结果如下：
工作中使用的：
CREATE PROCEDURE create_table1
@tab_name nvarchar(20)--定义参数
AS
--declare @tablename varchar(10)
declare @i int --定义变量
set @i=10 --变量赋值
while @i<20
begin
--create table ss(id varchar(10) not null,name varchar(20),address varchar(20));
exec(
'CREATE TABLE '+@tab_name+@i+'(
[ID] [decimal](18, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[TransNo] [nvarchar](12) NULL,
[BuCode] [nvarchar](6) NULL,
[BuName] [nvarchar](50) NULL,
[PosNum] [nvarchar](10) NULL,
[CardPrintNum] [nvarchar](10) NULL,
[TrDate] [nvarchar](10) NULL,
[TrTime] [nvarchar](10) NULL,
[Amount] [money] NULL,
[Fees] [money] NULL,
[Type] [nvarchar](10) NULL,
[ClearMarkCard] [nvarchar](1) NULL,
[ClearMarkBu] [nvarchar](1) NULL,
[ReMarks] [nvarchar](100) NULL,
[LiSign] [nvarchar](10) NULL,
[OffAmount] [money] NULL,
[FullPrice] [money] NULL,
[OPNum] [nvarchar](10) NULL,
CONSTRAINT [PK_'+@tab_name+@i+'] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)ON [PRIMARY]
ALTER TABLE '+@tab_name+@i+' ADD CONSTRAINT [DF_'+@tab_name+@i+'_Fees] DEFAULT ((0)) FOR [Fees]
ALTER TABLE '+@tab_name+@i+' ADD CONSTRAINT [DF_'+@tab_name+@i+'_ClearMarkCard] DEFAULT ((0)) FOR [ClearMarkCard]
ALTER TABLE '+@tab_name+@i+' ADD CONSTRAINT [DF_'+@tab_name+@i+'_ClearMarkBu] DEFAULT ((0)) FOR [ClearMarkBu]
--默认值为10个0
ALTER TABLE '+@tab_name+@i+' ADD CONSTRAINT [DF_'+@tab_name+@i+'_LiSign] DEFAULT (((''0000000000''))) FOR [LiSign]
ALTER TABLE '+@tab_name+@i+' ADD DEFAULT ((0)) FOR [OffAmount]
ALTER TABLE '+@tab_name+@i+' ADD DEFAULT ((0)) FOR [FullPrice]')
set @i = @i+1
END
GO
--执行存储过程
exec create_table1 'Tbl_Fz'