1. --缘由：要把数据库给别人，但每表中的纪录最多只想给3条，于是要删除多余数据行
2. --注意：为避免数据参照导致删除出问题，建议先删除原有外键。
3. --存储过程spDeleteData，删除表名参数所对应的表的纪录，最多保留3条纪录
4. --set rowcount 、动态SQL、全局临时表的应用
5.
6. create proc spDeleteData(@tn nvarchar(30)) as
7. begin
8. set nocount on
9. if object_id('tempdb..##t') is not null drop table ##t
10. exec('select count(*) cnt into ##t from ' + @tn)
11. declare @row int
12. select @row=cnt from ##t
13. if @row>3
14. begin
15. set @row=@row-3
16. set rowcount @row
17. exec('delete from ' + @tn)
18. set rowcount 0
19. end
20. end
21.
22. go
23.
24. --存储过程spDeleteDataByTableName，调用spDeleteData删除数据库中所用表的纪录，每表最多保留3条纪录
25. --set rowcount 、while循环、局部临时表的应用
26.
27. create proc spDeleteDataByTableName as
28. begin
29. if object_id('tempdb..#t') is not null drop table #t
30. select [name] into #t from sysobjects where type='u'
31. declare @tn nvarchar(30)
32. set rowcount 1
33. select @tn=[name] from #t
34. while @@rowcount>0
35. begin
36. exec spDeleteData @tn --嵌套调用存储过程spDeleteData
37. select @tn=[name] from #t
38. delete from #t
39. end
40. set rowcount 0
41. end
42.
43. go
44.
45. exec spDeleteDataByTableName--调用存储过程spDeleteDataByTableName
