我想写一个存储过程，内容是现有一个存储过程假如叫SP_tool，该存储过程接受一个参数，如果传入参数叫TA,那么执行一个查找操作，在当前数据库中查找有没有名字叫TA的表，如果有 就自动建立一个存储过程对该表进行插入工作。如果没有这个名称的表 直接返回。请牛人们帮忙帮忙，我才开始学，很菜。谢谢啦！！！
------解决方案--------------------
建表都有哪些字段,插入都插入什么值啊?楼主没说
create proc sp_tool(@tbname varchar(100))
as
declare @sql varchar(1000)
if not exists(select 1 from sysobjects where xtype= 'U ' and [email protected])
begin
set @sql= 'create table '[email protected]+ '(id int identity(1,1)) '
exec(@sql)
end
exec sp_tool 'ta '
------解决方案--------------------
create proc test(@name sysname)
as
begin
if exists(select 1 from sysobjects where [email protected] and xtype= 'U ')
exec( 'insert '[email protected] ' select * from tb '+ ') '--有把tb表数据导入变量表
else
print '没有此表 '
return
end
------解决方案--------------------
create proc SP_tool
(@tbname varchar(20))
as
declare @nsqlstr nvarchar(2000)
declare @tmpsqlstr nvarchar(2000)
declare @name varchar(20)
declare @NewProcname varchar(20)
declare @sysobjectsID int
declare @xtypename varchar(20)
declare @length int
declare @status int
set @NewProcname= 'Insert_ '[email protected]
set @tmpsqlstr= ' '
set @nsqlstr= ' '
if exists (select * from sysobjects where [email protected] and type= 'u ')
begin
select @sysobjectsID=id from sysobjects where [email protected] and type= 'u '
set @nsqlstr= 'if exists (select * from sysobjects where name= ' ' '[email protected]+ ' ' ' and type= ' 'p ' ') drop proc '[email protected]
set @nsqlstr= 'create proc '[email protected]+ '( '
declare cur cursor for
select distinct a.name,b.name,b.status,a.length from dbo.syscolumns a left join systypes b
on a.xtype=b.xusertype where [email protected]
open cur
fetch next from cur into @name,@xtypename,@status,@length
while @@fetch_status=0
begin
set @[email protected]+ '@ '[email protected]+ ' '[email protected]
if @status=2
begin
set @[email protected]+ '( '+rtrim(cast(@length as varchar(10)))+ ') '
end
set @[email protected]+ ', '
set @[email protected]+ '@ '[email protected]+ ', '
fetch next from cur into @name,@xtypename,@status,@length
end
close cur
deallocate cur
select @nsqlstr=left(rtrim(@nsqlstr),len(rtrim(@nsqlstr))-1)
set @[email protected]+ ') '+ ' as insert into '[email protected]+ ' values ( '
set @nsqlstr=rtrim(@nsqlstr)+rtrim(@tmpsqlstr)
select @tmpsqlstr=left(rtrim(@nsqlstr),len(rtrim(@nsqlstr))-1)
set @[email protected]+ ') '
exec (@nsqlstr)
end
------解决方案--------------------
--接上
IF @FLAT=2 --修改
BEGIN
DECLARE @UPDATE_SQLROC VARCHAR(8000)
DECLARE @UPDATE_SQL VARCHAR(8000)
DECLARE @UPDATE_PARAMETER VARCHAR(1000)
DECLARE @UPDATE_DESCRIPTION VARCHAR(1000)
DECLARE @UPDATE_REMARK VARCHAR(1000)
DECLARE @UPDATE_KEY_COLUMN VARCHAR(1000)
DECLARE @UPDATE_COLUMN VARCHAR(5000)
SELECT @UPDATE_SQLROC= ' ',
@UPDATE_SQL= ' ',@UPDATE_PARAMETER= ' ',@UPDATE_DESCRIPTION= ' ',
@UPDATE_REMARK= ' ',@UPDATE_KEY_COLUMN= ' ',@UPDATE_COLUMN= ' '
SET @[email protected]_SQLROC+ 'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID( ' 'SP_ '[email protected]+ '_Update ' ') AND XTYPE IN (N ' 'P ' ')) '+CHAR(10)