UQtKEtPH

1/* begin original */
2set ANSI_NULLS ON
3set QUOTED_IDENTIFIER ON
4GO
5
6CREATE PROCEDURE [dbo].[Populate_mt_Counts] AS
7BEGIN
8
9  -- Create cursor for looping through table with list of table names
10  DECLARE #curTableNames SCROLL CURSOR FOR 
11    SELECT name
12     FROM typetable
13       ORDER BY name
14
15  DECLARE @tablename varchar(100)
16DECLARE @ddl varchar(max)
17          
18  OPEN #curTableNames
19
20  FETCH NEXT FROM #curTableNames INTO @tablename
21
22  -- Loop through list of tables 
23  WHILE (@@fetch_status <> -1) 
24    BEGIN
25
26  SELECT @ddl = 'update typetable
27         set t1 = 
28(select count(*) from ' + @tablename + ' where mtypeid= 1)
29where ' + quotename(@tablename,'''') + ' = name'
30        
31  PRINT @ddl
32  EXEC (@DDL)
33
34  SELECT @ddl = 'update typetable
35         set t2= 
36(select count(*) from ' + @tablename + ' where mtypeid= 2)
37where ' + quotename(@tablename,'''') + ' = name'
38        
39  PRINT @ddl
40  EXEC (@DDL)
41
42    FETCH NEXT FROM #curTableNames INTO @tablename
43    END    
44    
45
46  -- Get rid of the cursor
47  CLOSE #curTableNames
48  DEALLOCATE #curTableNames
49END
50/* end original */
51
52/* begin revision */
53IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[typetable]') AND name = N'ix_typetables_name')
54  DROP INDEX [ix_typetable_name] ON [dbo].[typetable] WITH ( ONLINE = OFF )
55go
56
57create index
58   [ix_typetable_name]
59on
60   [dbo].[typetable](tablename asc)
61go
62
63if (object_id('[dbo].[Populate_mt_Counts]') is not null)
64  drop proc [dbo].[Populate_mt_Counts]
65go
66
67create proc [dbo].[Populate_mt_Counts]
68as
69set nocount on
70set transaction isolation level read uncommitted
71
72declare
73  @tn nvarchar(200)
74  , @sql nvarchar(max)
75
76declare c cursor fast_forward for
77  select
78    name
79  from
80    typetable
81
82open c
83while 1=1
84begin
85  fetch next from c into @tn
86  if @@fetch_status <> 0 break
87
88  set @sql = 
89'declare @c1 int, @c2 int;
90select
91  @c1 = sum(case mtypeid when 1 then 1 else 0 end)
92  , @c2 = sum(case mtypeid when 2 then 1 else 0 end)
93from
94  [dbo].[' + @tn + '];
95update
96  [dbo].[typetable]
97set
98  t1 = @c1
99  , t2 = @c2
100where
101  name = @t;'
102  exec sp_executesql @sql, N'@t varchar(100)', @t = @tn
103end
104close c
105deallocate c
106go
107/* end revision */