请问SQL能解决这个问题吗

ItSeeker 2002-07-31 02:49:24
一个procedure,根据参数来建表,怎样实现啊!当然除了用VB等前端实现方法

create procedure sp_creator
@tablename nvarchar(255)
as
begin
create table @tablename ([name] nvarchar(30) )
--这样当然不对了,我只是这样描述@tablename 是变量
end
...全文
44 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
playyuer 2002-07-31
  • 打赏
  • 举报
回复
动态SQL

execute (@SQLDDL)
hubinasm 2002-07-31
  • 打赏
  • 举报
回复
CREATE PROCEDURE Create_io_stock AS
declare @stock varchar(7)
declare @stock2 varchar(7)
set @stock = "i"+left(convert(char,dateadd(m,1,getdate()),112),6)
set @stock2 = "o"+left(convert(char,dateadd(m,1,getdate()),112),6)
declare @companyid int
declare @str varchar(1000)
declare @companycount int
declare company_cur scroll cursor
for select company_id from company
open company_cur
/*select hubin = @@cursor_rows*/
fetch first from company_cur into @companyid


while @@fetch_status = 0
begin
/*
set @str = 'select ' + @companycount +' = count(*) from io_company where company_id = '
set @str = @str + @companyid +' and company_table = '
set @str = @str + rtrim(@stock+convert(varchar(20),@companyid))
exec(@str)
if @companycount = 0
*/
begin
/*建立各个公司入库条码表*/
set @str = "CREATE TABLE " + rtrim(@stock+convert(varchar(20),@companyid)) + " ("
set @str = @str + "[in_info_id] [int] IDENTITY (1, 1) NOT NULL ,"
set @str = @str + "[in_inwa_id] [int] NOT NULL ,"
set @str = @str + "[in_idetail_id] [int] NOT NULL ,"
set @str = @str + "[in_info_xianghao] [varchar] (50) NULL ,"
set @str = @str + "[in_info_code] [varchar] (50) NULL ,"
set @str = @str + "[in_info_dc_id] [int] NOT NULL ,"
set @str = @str + "[in_info_prod_id] [int] NOT NULL ,"
set @str = @str + "[in_info_companyid] [int] NOT NULL ,"
set @str = @str + "[in_info_date] [datetime] NULL "
set @str = @str + ") ON [PRIMARY]"
exec(@str)
insert into io_company(company_id,company_table,company_date,io_flag) values(@companyid,rtrim(@stock+convert(varchar(20),@companyid)),getdate(),1)

/*建立各个公司出库条码表*/
set @str = "CREATE TABLE " + rtrim(@stock2+convert(varchar(20),@companyid)) + " ("
set @str = @str + "[out_info_id] [int] IDENTITY (1, 1) NOT NULL ,"
set @str = @str + "[out_elink_id] [int] NOT NULL ,"
set @str = @str + "[out_detail_id] [int] NULL ,"
set @str = @str + "[out_dc_id] [int] NULL ,"
set @str = @str + "[out_prod_id] [int] NULL ,"
set @str = @str + "[out_companyid] [int] NULL ,"
set @str = @str + "[out_info_xianghao] [varchar] (50) NULL ,"
set @str = @str + "[out_info_code] [varchar] (50) NULL ,"
set @str = @str + "[out_date] [datetime] NULL "
set @str = @str + ") ON [PRIMARY]"
exec(@str)
insert into io_company(company_id,company_table,company_date,io_flag) values(@companyid,rtrim(@stock2+convert(varchar(20),@companyid)),getdate(),2)

end
fetch next from company_cur into @companyid
end
close company_cur
deallocate company_cur
liujidong 2002-07-31
  • 打赏
  • 举报
回复
create procedure sp_creator
@tablename varchar(255)
as
begin
exec('create table '+@tablename+' (name varchar(30))')
--这样当然就对了,呵呵
end

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧