try:
-----------------------------------------------------------------------
--创建通用存储过程
create procedure sp_test(@tname varchar(100))
as
begin
declare @name varchar(100),@xtype int,@v varchar(8000),@s varchar(8000)
set @v = 'declare t_cursor cursor for select name,xtype from syscolumns where id=object_id('''+@tname+''')'
exec(@v)
open t_cursor
fetch next from t_cursor into @name,@xtype
select @s = '',@v = ''
while @@fetch_status = 0
begin
set @s = @s + ','+@name
set @v = @v + ','+case when @xtype in(173,175,34,239,99,231,35,165,167) then ''''''
else '0'
end
fetch next from t_cursor into @name,@xtype
end
close t_cursor
deallocate t_cursor
set @s = 'insert into '+@tname+'('+stuff(@s,1,1,'')+') values('+stuff(@v,1,1,'')+')'
exec(@s)
end
go
--创建测试数据表
create table test(id int not null,name varchar(10) not null,dates datetime not null)
go
--执行存储过程对test表插入空行
exec sp_test 'test'
--查看存储过程执行结果
select * from test
/*
id name dates
-- ---- -----------------------
0 1900-01-01 00:00:00.000
*/
--清除测试环境
drop table test
drop procedure sp_test