SQL存储过程的问题 请牛人进！！！！！！！！！
我想写一个存储过程，内容是现有一个存储过程假如叫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 name=@tbname)
begin
set @sql= 'create table '+@tbname+ '(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 name=@name and xtype= 'U ')
exec( 'insert '+@name ' 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_ '+@tbname
set @tmpsqlstr= ' '
set @nsqlstr= ' '
if exists (select * from sysobjects where name=@tbname and type= 'u ')
begin
select @sysobjectsID=id from sysobjects where name=@tbname and type= 'u '
set @nsqlstr= 'if exists (select * from sysobjects where name= ' ' '+@NewProcname+ ' ' ' and type= ' 'p ' ') drop proc '+@NewProcname
set @nsqlstr= 'create proc '+@NewProcname+ '( '
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 a.id=@sysobjectsID
open cur
fetch next from cur into @name,@xtypename,@status,@length
while @@fetch_status=0
begin
set @nsqlstr=@nsqlstr+ '@ '+@name+ ' '+@xtypename
if @status=2
begin
set @nsqlstr=@nsqlstr+ '( '+rtrim(cast(@length as varchar(10)))+ ') '
end
set @nsqlstr=@nsqlstr+ ', '
set @tmpsqlstr=@tmpsqlstr+ '@ '+@name+ ', '
fetch next from cur into @name,@xtypename,@status,@length
end
close cur
deallocate cur
select @nsqlstr=left(rtrim(@nsqlstr),len(rtrim(@nsqlstr))-1)
set @nsqlstr=@nsqlstr+ ') '+ ' as insert into '+@tbname+ ' values ( '
set @nsqlstr=rtrim(@nsqlstr)+rtrim(@tmpsqlstr)
select @tmpsqlstr=left(rtrim(@nsqlstr),len(rtrim(@nsqlstr))-1)
set @nsqlstr=@tmpsqlstr+ ') '
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 @UPDATE_SQLROC=@UPDATE_SQLROC+ 'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID( ' 'SP_ '+@TABLENAME+ '_Update ' ') AND XTYPE IN (N ' 'P ' ')) '+CHAR(10)
SET @UPDATE_SQLROC=@UPDATE_SQLROC+SPACE(5)+ 'DROP PROC SP_ '+@TABLENAME+ '_Update '+CHAR(10)
SET @UPDATE_SQLROC=@UPDATE_SQLROC+ ' GO '
SELECT @UPDATE_PARAMETER=@UPDATE_PARAMETER+SPACE(4)+ '@ '+LTRIM(NAME)+SPACE(2)+
CASE WHEN xtype=34 THEN 'image '
WHEN xtype=35 THEN 'text '
WHEN xtype=36 THEN 'uniqueidentifier '
WHEN xtype=48 THEN 'tinyint '
WHEN xtype=52 THEN 'smallint '
WHEN xtype=56 THEN 'int '
WHEN xtype=58 THEN 'smalldatetime '
WHEN xtype=59 THEN 'real '
WHEN xtype=60 THEN 'money '
WHEN xtype=61 THEN 'datetime '
WHEN xtype=62 THEN 'float '
WHEN xtype=98 THEN 'sql_variant '
WHEN xtype=99 THEN 'ntext '
WHEN xtype=104 THEN 'bit '
WHEN xtype=106 THEN 'decimal '
WHEN xtype=108 THEN 'numeric '
WHEN xtype=122 THEN 'smallmoney '
WHEN xtype=127 THEN 'bigint '
WHEN xtype=165 THEN 'varbinary '
WHEN xtype=167 THEN 'varchar '+ '( '+LTRIM(length)+ ') '
WHEN xtype=173 THEN 'binary '
WHEN xtype=175 THEN 'char '+ '( '+LTRIM(length)+ ') '
WHEN xtype=189 THEN 'timestamp '
WHEN xtype=231 THEN 'nvarchar '+ '( '+LTRIM(length)+ ') '
WHEN xtype=239 THEN 'nchar '+ '( '+LTRIM(length)+ ') '
WHEN xtype=241 THEN 'xml '
WHEN xtype=251 THEN 'sysname ' END+ ', '+CHAR(10)
FROM SYSCOLUMNS A
WHERE ID=OBJECT_ID( ' '+@TABLENAME+ ' ')
SET NOCOUNT ON
CREATE TABLE #(TABLE_QUALIFIER VARCHAR(100),
TABLE_OWNER VARCHAR(100),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(100),
KEY_SEQ VARCHAR(50),
PK_NAME VARCHAR(100))
INSERT INTO # EXEC SP_PKEYS @TABLENAME
SELECT @UPDATE_REMARK=@UPDATE_REMARK+ ', '+COLUMN_NAME+ '=@ '+COLUMN_NAME
FROM #
SELECT @UPDATE_KEY_COLUMN=@UPDATE_KEY_COLUMN+ ', '+NAME+ '=@ '+NAME
FROM SYSCOLUMNS A
WHERE ID=OBJECT_ID( ' '+@TABLENAME+ ' ')
AND NAME NOT IN (SELECT COLUMN_NAME FROM #)
DROP TABLE #
SET NOCOUNT OFF
IF DATALENGTH(@UPDATE_PARAMETER)> 0
BEGIN
SET @UPDATE_PARAMETER=LEFT(@UPDATE_PARAMETER,LEN(@UPDATE_PARAMETER)-2)
SELECT @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+@UPDATE_DESCRIPTION+ 'CREATE PROC USP_ '+@TABLENAME+ '_Update '
SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(13)+CHAR(10)+@UPDATE_PARAMETER+CHAR(10)
SET @UPDATE_SQLROC=@UPDATE_SQLROC+ 'AS '+CHAR(10)+ 'BEGIN '
SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+SPACE(4)+ 'SET NOCOUNT ON '+CHAR(10)
SET @UPDATE_SQL=SPACE(8)+ 'UPDATE '+@TABLENAME+CHAR(10)+SPACE(8)+ 'SET '
SET @UPDATE_SQLROC=@UPDATE_SQLROC+@UPDATE_SQL+STUFF(@UPDATE_KEY_COLUMN,1,1, ' ')
SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+SPACE(8)+ 'FROM '+@TABLENAME+CHAR(10)+SPACE(8)+ 'WHERE '+STUFF(@UPDATE_REMARK,1,1, ' ')+ ' '
SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+SPACE(8)+ 'SELECT _ROWCOUNT=@@ROWCOUNT '
SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+SPACE(4)+ 'SET NOCOUNT OFF '
SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+ 'END '
PRINT @UPDATE_SQLROC+CHAR(13)+CHAR(10)+ 'GO '
END
END
IF @FLAT=3 --刪除
BEGIN
DECLARE @DELETE_SQLROC VARCHAR(8000)
DECLARE @DELETE_SQL VARCHAR(8000)
DECLARE @DELETE_PARAMETER VARCHAR(1000)
DECLARE @DELETE_DESCRIPTION VARCHAR(1000)
DECLARE @DELETE_REMARK VARCHAR(1000)
DECLARE @DELETE_KEY_COLUMN VARCHAR(1000)
SELECT @DELETE_SQLROC= ' ',@DELETE_SQL= ' ',@DELETE_PARAMETER= ' ',@DELETE_DESCRIPTION= ' ',
@DELETE_REMARK= ' ',@DELETE_KEY_COLUMN= ' '
SET @DELETE_SQLROC=@DELETE_SQLROC+ 'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID( ' 'SP_ '+@TABLENAME+ '_Remove ' ') AND XTYPE IN (N ' 'P ' ')) '+CHAR(10)
SET @DELETE_SQLROC=@DELETE_SQLROC+SPACE(5)+ 'DROP PROC SP_ '+@TABLENAME+ '_Remove '+CHAR(10)
SET @DELETE_SQLROC=@DELETE_SQLROC+ ' GO '
SET NOCOUNT ON
CREATE TABLE #_#(TABLE_QUALIFIER VARCHAR(100),
TABLE_OWNER VARCHAR(100),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(100),
KEY_SEQ VARCHAR(50),
PK_NAME VARCHAR(100))
INSERT INTO #_# EXEC SP_PKEYS @TABLENAME
SELECT @DELETE_KEY_COLUMN=@DELETE_KEY_COLUMN+ ', '+COLUMN_NAME+ '=@ '+COLUMN_NAME,
@DELETE_REMARK=@DELETE_REMARK+ ',@ '+COLUMN_NAME
FROM #_#
SELECT @DELETE_PARAMETER=@DELETE_PARAMETER+SPACE(4)+ '@ '+LTRIM(NAME)+SPACE(2)+
CASE WHEN xtype=34 THEN 'image '
WHEN xtype=35 THEN 'text '
WHEN xtype=36 THEN 'uniqueidentifier '
WHEN xtype=48 THEN 'tinyint '
WHEN xtype=52 THEN 'smallint '
WHEN xtype=56 THEN 'int '
WHEN xtype=58 THEN 'smalldatetime '
WHEN xtype=59 THEN 'real '
WHEN xtype=60 THEN 'money '
WHEN xtype=61 THEN 'datetime '
WHEN xtype=62 THEN 'float '
WHEN xtype=98 THEN 'sql_variant '
WHEN xtype=99 THEN 'ntext '
WHEN xtype=104 THEN 'bit '
WHEN xtype=106 THEN 'decimal '
WHEN xtype=108 THEN 'numeric '
WHEN xtype=122 THEN 'smallmoney '
WHEN xtype=127 THEN 'bigint '
WHEN xtype=165 THEN 'varbinary '
WHEN xtype=167 THEN 'varchar '+ '( '+LTRIM(length)+ ') '
WHEN xtype=173 THEN 'binary '
WHEN xtype=175 THEN 'char '+ '( '+LTRIM(length)+ ') '
WHEN xtype=189 THEN 'timestamp '
WHEN xtype=231 THEN 'nvarchar '+ '( '+LTRIM(length)+ ') '
WHEN xtype=239 THEN 'nchar '+ '( '+LTRIM(length)+ ') '
WHEN xtype=241 THEN 'xml '
WHEN xtype=251 THEN 'sysname ' END+ ', '+CHAR(10)
FROM SYSCOLUMNS A
WHERE ID=OBJECT_ID( ' '+@TABLENAME+ ' ')
AND NAME IN (SELECT COLUMN_NAME FROM #_#)
DROP TABLE #_#
SET NOCOUNT OFF
IF DATALENGTH(@DELETE_PARAMETER)> 0
BEGIN
SET @DELETE_PARAMETER=LEFT(@DELETE_PARAMETER,LEN(@DELETE_PARAMETER)-2)
SELECT @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+@DELETE_DESCRIPTION+ 'CREATE PROC USP_ '+@TABLENAME+ '_Remove '
SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(13)+CHAR(10)+@DELETE_PARAMETER+CHAR(10)
SET @DELETE_SQLROC=@DELETE_SQLROC+ 'AS '+CHAR(10)+ 'BEGIN '
SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+SPACE(4)+ 'SET NOCOUNT ON '
SET @DELETE_SQL= 'DELETE '+@TABLENAME+CHAR(10)+SPACE(8)+ 'WHERE '
SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+SPACE(8)+@DELETE_SQL+STUFF(@DELETE_KEY_COLUMN,1,1, ' ')
SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+SPACE(8)+ 'SELECT _ROWCOUNT=@@ROWCOUNT '
SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+SPACE(4)+ 'SET NOCOUNT OFF '
SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+ 'END '
PRINT @DELETE_SQLROC+CHAR(13)+CHAR(10)+ 'GO '
END
END
END
[解决办法]
declare @TA varchar(30)
set @TA= 'a '
if exists(select 1 from sysobjects where name=@TA and xtype= 'U ')
begin
declare @s varchar(4000)
declare @s1 varchar(2000)
declare @s2 varchar(2000)
set @s= ' '
set @s1= ' '
set @s2= ' '
select @s2=@s2+ '@ '+ sc.name + ', ',
@s1=@s1+ '@ '+ sc.name + ' '+(case when st.name like '%char% ' then st.name + '( '+ cast(sc.length as varchar) + ') '
when st.name like '%decimal% ' then st.name + '( '+ cast(sc.xprec as varchar) + ', ' + cast(sc.xscale as varchar) + ') '
else st.name end)+ ', '
from dbo.syscolumns sc
join dbo.sysobjects so on sc.id=so.id and so.name=@TA
join dbo.systypes st on st.xtype=sc.xtype and st.xusertype=sc.xusertype
where sc.status & 128=0--128:标识列
if @s1 <> ' ' set @s1=left(@s1,len(@s1)-1)
if @s2 <> ' ' set @s2=left(@s2,len(@s2)-1)
set @s= 'Create Proc Insert_ '+ @TA + ' '+@s1+ ' AS
'+ 'insert into '+ @TA + ' values( '+@s2+ ') '
exec(@s)
end
else print '没有 '+@TA+ '表 '
