Когда этот ублюдочный микрософтовский проект попал ко мне в руки - он весь выглядел примерно вот так - прямые записи в базу (включая даже создание новых таблиц) - непосредственно из кода. Причем даже без обрабобтки ошибок!

Как я выяснил на тестах - именно это и было его проблемой.

Его призводительность и надежность мне удалось повысить, только отправив ф топку весь код этого проекта и создав вместо этого ублюдочного кода примерно вот такой код:

00001: ALTER procedure [dbo].[CreateNewCustomer]  
00002: @CustomerCode nvarchar(50), 
00003: @CustomerName nvarchar(200), 
00004: @Description ntext =NULL, 
00005: @Dashboard text =NULL,
00006: @Header ntext =NULL, 
00007: @Footer ntext =NULL,
00008: @UserID int
00009: as
00010: BEGIN TRAN
00011: 
00012: INSERT [Customer]([CustomerCode] ,[CustomerName] ,[Description] ,[Dashboard] ,[Created] ,[Header] ,[Footer])
00013: VALUES (@CustomerCode, @CustomerName, @Description, @Dashboard, getdate(), @Header, @Footer)
00014: declare @NewID int
00015: select  @NewID=scope_identity()
00016: IF @@ERROR <> 0 BEGIN
00017:     ROLLBACK
00018:     RETURN 0
00019: END
00020: 
00021: INSERT [CIType] ([CustomerID] ,[CITypeName] ,[Description] ,[ExtTable] ,[Created] , [CodePrefix], [Img_Logo])
00022: VALUES  (@NewID, 'Пользователи', 'Список пользователей системы','T_' + @CustomerCode + '_UserAccount', Getdate(), 'USR-', 'ico_user.ico')
00023: Declare @UserTable int
00024: select  @UserTable=scope_identity()
00025: IF @@ERROR <> 0 BEGIN
00026:     ROLLBACK
00027:     RETURN 0
00028: END
00029: 
00030: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00031: VALUES (@UserTable, 12, 'Create Customer ','CustomerCode=' + @CustomerCode +',CustomerName='+ @CustomerName + ',CustomerID=' + cast(@NewID as nvarchar), Getdate(), @UserID,0)
00032: IF @@ERROR <> 0 BEGIN
00033:     ROLLBACK
00034:     RETURN 0
00035: END
00036: 
00037: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00038: VALUES (@UserTable, 5, 'Create UserTable ','T_' + @CustomerCode + '_UserAccount', Getdate(), @UserID, 0)
00039: IF @@ERROR <> 0 BEGIN
00040:     ROLLBACK
00041:     RETURN 0
00042: END
00043: 
00044: INSERT [CIType] ([CustomerID] ,[CITypeName] ,[Description] ,[ExtTable] ,[Created] , [CodePrefix], [Img_Logo])
00045: VALUES  (@NewID, 'Роли', 'Список ролей в системе','T_' + @CustomerCode + '_Role', Getdate(), 'ROLE-', 'ico_default.ico')
00046: Declare @RoleTable int
00047: select  @RoleTable=scope_identity()
00048: IF @@ERROR <> 0 BEGIN
00049:     ROLLBACK
00050:     RETURN 0
00051: END
00052: 
00053: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00054: VALUES (@RoleTable, 5, 'Create RoleTable ','T_' + @CustomerCode + '_Role', Getdate(), @UserID, 0)
00055: IF @@ERROR <> 0 BEGIN
00056:     ROLLBACK
00057:     RETURN 0
00058: END
00059: 
00060: declare @Str1 nvarchar(1000)
00061: select  @Str1='CREATE TABLE T_' + @CustomerCode + '_UserAccount('+
00062: '    [ID] [int] IDENTITY(1,1) NOT NULL,
00063:     [Code] [nvarchar](20) NOT NULL,
00064:     [Name] [nvarchar](100) NULL,
00065:     [Created] [datetime] NOT NULL DEFAULT (getdate()),
00066:     [Modified] [datetime] NOT NULL DEFAULT (getdate()),
00067:     [CreatorID] [int] NOT NULL,
00068:     [ModifierID] [int] NOT NULL,
00069:     [Login] [nvarchar](100) NULL,
00070:     [Email] [nvarchar](100) NULL,
00071:     [Passwd] binary(16) NULL,
00072:     [DepartmantID] [int] NULL,
00073:     [Image32] [varbinary](max) NULL,
00074: PRIMARY KEY CLUSTERED 
00075: (
00076:     [Code] ASC
00077: )
00078: ) ON [PRIMARY]'
00079: exec    (@Str1)
00080: IF @@ERROR <> 0 BEGIN
00081:     ROLLBACK
00082:     RETURN 0
00083: END
00084: 
00085: declare @Str2 nvarchar(1000)
00086: select  @Str2= 'CREATE TABLE T_' + @CustomerCode + '_Role('+
00087: '    [ID] [int] IDENTITY(1,1) NOT NULL,
00088:     [Code] [nvarchar](20) NOT NULL,
00089:     [Name] [nvarchar](100) NULL,
00090:     [Created] [datetime] NOT NULL DEFAULT (getdate()),
00091:     [Modified] [datetime] NOT NULL DEFAULT (getdate()),
00092:     [CreatorID] [int] NOT NULL,
00093:     [ModifierID] [int] NOT NULL,
00094:     [Description] [ntext] NULL,
00095:     [DomainGroup] [nvarchar](100) NULL,
00096:     [Image32] [varbinary](max) NULL,
00097: PRIMARY KEY CLUSTERED  
00098: ( 
00099:     [Code] ASC
00100: )
00101: ) ON [PRIMARY]'
00102: Exec (@Str2)
00103: IF @@ERROR <> 0 BEGIN
00104:     ROLLBACK
00105:     RETURN 0
00106: END
00107: 
00108: declare @Str3 nvarchar(1000)
00109: select  @Str3='
00110: INSERT T_' + @CustomerCode + '_UserAccount
00111:            ([Code]
00112:            ,[Name]
00113:            ,[Login]
00114:            ,[Passwd],
00115:            [CreatorID],
00116:            [ModifierID])
00117: VALUES     (''USR-0000'', ''Administrator'', ''Administrator'' , NULL, 0,0)'
00118: exec   (@Str3)
00119: IF @@ERROR <> 0 BEGIN
00120:     ROLLBACK
00121:     RETURN 0
00122: END
00123: 
00124: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00125: VALUES (@UserTable, 5, 'Create NewUser','Administrator', Getdate(), @UserID, 0)
00126: IF @@ERROR <> 0 BEGIN
00127:     ROLLBACK
00128:     RETURN 0
00129: END
00130: 
00131: declare @Str4 nvarchar(1000)
00132: select  @Str4='
00133: INSERT T_' + @CustomerCode + '_Role
00134:            ([Code]
00135:            ,[Name]
00136:            ,[Created]
00137:            ,[Modified]
00138:            ,[CreatorID]
00139:            ,[ModifierID])
00140: VALUES  (''ROLE-0000'', ''Administrators'',0,0,0,0)'
00141: exec    (@Str4)
00142: IF @@ERROR <> 0 BEGIN
00143:     ROLLBACK
00144:     RETURN 0
00145: END
00146: 
00147: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00148: VALUES (@RoleTable, 5, 'Create Role ','Administrators', Getdate(), @UserID, 0)
00149: IF @@ERROR <> 0 BEGIN
00150:     ROLLBACK
00151:     RETURN 0
00152: END
00153: 
00154: UPDATE [Customer]
00155: SET    RoleCITypeID=@RoleTable, UserCITypeID=@UserTable, AdminRoleID=1
00156: WHERE  ID=@NewID
00157: IF @@ERROR <> 0 BEGIN
00158:     ROLLBACK
00159:     RETURN 0
00160: END
00161: 
00162: INSERT [CIRelationType] ([CustomerID] ,[ForParent] ,[ForChild] ,[Created] ,[ParentTypeID] ,[ChildTypeID])
00163: VALUES (@NewID,'Входит в роль','Участник роли',Getdate(),@RoleTable,@UserTable)
00164: Declare @RelationType int
00165: select  @RelationType=scope_identity()
00166: IF @@ERROR <> 0 BEGIN
00167:     ROLLBACK
00168:     RETURN 0
00169: END
00170: 
00171: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00172: VALUES (@UserTable, 13, 'Add RelationType', 'RoleTable=' + cast(@RoleTable as nvarchar)+ ',UserTable=' +  cast(@UserTable as nvarchar) + ',@RelationType=' + cast(@RelationType as nvarchar), Getdate(), @UserID, 0)
00173: IF @@ERROR <> 0 BEGIN
00174:     ROLLBACK
00175:     RETURN 0
00176: END
00177: 
00178: INSERT [CIRelation]([CITypeID1],[CIID1] ,[CITypeID2] ,[CIID2] ,[RelationTypeID])
00179: VALUES  (@RoleTable ,1 ,@UserTable, 1, @RelationType)
00180: IF @@ERROR <> 0 BEGIN
00181:     ROLLBACK
00182:     RETURN 0
00183: END
00184: 
00185: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00186: VALUES (@UserTable, 7, 'Add Relation', 'RoleTable=' + cast(@RoleTable as nvarchar)+ ',UserTable=' +  cast(@UserTable as nvarchar) + ',@RelationType=' + cast(@RelationType as nvarchar) , Getdate(), @UserID, 0)
00187: IF @@ERROR <> 0 BEGIN
00188:     ROLLBACK
00189:     RETURN 0
00190: END
00191: 
00192: INSERT [AppAccess] ([CustomerID],[LevelCode],[RoleID],[AccessCode],[CITypeID],[CIAttrID],[CIID],[Created])
00193: VALUES (@NewID,'TYPE', 1, 'SUID' ,@RoleTable, 0, 0, GetDate())
00194: IF @@ERROR <> 0 BEGIN
00195:     ROLLBACK
00196:     RETURN 0
00197: END
00198: 
00199: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00200: VALUES (@RoleTable, 14, 'Add Access ','Standard', Getdate(), @UserID, 0)
00201: IF @@ERROR <> 0 BEGIN
00202:     ROLLBACK
00203:     RETURN 0
00204: END
00205: 
00206: INSERT [AppAccess] ([CustomerID],[LevelCode],[RoleID],[AccessCode],[CITypeID],[CIAttrID],[CIID],[Created])
00207: VALUES (@NewID,'TYPE', 1, 'SUID' ,@UserTable, 0, 0, GetDate())
00208: IF @@ERROR <> 0 BEGIN
00209:     ROLLBACK
00210:     RETURN 0
00211: END
00212: 
00213: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00214: VALUES (@UserTable, 14, 'Add Access ','Standard', Getdate(), @UserID, 0)
00215: IF @@ERROR <> 0 BEGIN
00216:     ROLLBACK
00217:     RETURN 0
00218: END
00219: 
00220: 
00221: INSERT INTO [CITemplate] ([CITypeID] ,[RoleID] ,[TemplateName] ,[ListTemplate]  ,[CardTemplate]
00222: ,[Created] ,[DefaultFlag] ,[CommonFlag])
00223: VALUES      (@RoleTable, 1, 'Standard', '<tr><td width=100>[Code]<td><td>#Name#</td><td>#Доменная группа#</td></tr>',
00224: '<table width=100% border=1 cellpadding=1 cellspacing=0><tr><td width=100>[[Code]]</td><td>#Code#</td></tr>'+
00225: '<tr><td>[[Name]]</td><td>#Name#</td></tr>'+
00226: '<tr><td>[[Описание]]</td><td>#Описание#</td></tr>'+
00227: '</table>', Getdate(),1,0)
00228: IF @@ERROR <> 0 BEGIN
00229:     ROLLBACK
00230:     RETURN 0
00231: END
00232: 
00233: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00234: VALUES (@RoleTable, 15, 'Add Template ','Standard', Getdate(), @UserID, 0)
00235: IF @@ERROR <> 0 BEGIN
00236:     ROLLBACK
00237:     RETURN 0
00238: END
00239: 
00240: INSERT INTO [CITemplate] ([CITypeID] ,[RoleID] ,[TemplateName] ,[ListTemplate]  ,[CardTemplate]
00241: ,[Created] ,[DefaultFlag] ,[CommonFlag])
00242: VALUES      (@UserTable, 1, 'Standard', '<tr><td>[Code]</td><td>#Name#</td><td>#Login#</td><td>#Email#</td></tr>',
00243: '<table width=100% border=1>'+
00244: '<tr><td>[[Code]]</td><td>#Code#</td></tr>'+
00245: '<tr><td>[[Name]]</td><td>#Name#</td></tr>'+
00246: '<tr><td>[[Login]]</td><td>#Login#</td></tr>'+
00247: '<tr><td>[[Email]]</td><td>#Email#</td></tr>'+
00248: '<tr><td>[[Пароль]]</td><td>#Пароль#</td></tr>'+
00249: '</table>', Getdate(),1,0)
00250: IF @@ERROR <> 0 BEGIN
00251:     ROLLBACK
00252:     RETURN 0
00253: END
00254: 
00255: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00256: VALUES (@UserTable, 15, 'Add Template ','Standard', Getdate(), @UserID, 0)
00257: IF @@ERROR <> 0 BEGIN
00258:     ROLLBACK
00259:     RETURN 0
00260: END
00261: 
00262: INSERT [CIAttr] ([CITypeID] ,[AttrName] ,[AttrTypeID] ,[FieldName] ,[Created] ,[NotForHead] )
00263: VALUES (@UserTable, 'Code' , 10002, 'Code', Getdate(),0)
00264: IF @@ERROR <> 0 BEGIN
00265:     ROLLBACK
00266:     RETURN 0
00267: END
00268: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00269: VALUES (@UserTable, 11, 'Add Attr ','Code', Getdate(), @UserID, 0)
00270: IF @@ERROR <> 0 BEGIN
00271:     ROLLBACK
00272:     RETURN 0
00273: END
00274: INSERT [CIAttr] ([CITypeID] ,[AttrName] ,[AttrTypeID] ,[FieldName] ,[Created] ,[NotForHead] )
00275: VALUES (@UserTable, 'Name' , 10000, 'Name', Getdate(),0)
00276: IF @@ERROR <> 0 BEGIN
00277:     ROLLBACK
00278:     RETURN 0
00279: END
00280: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00281: VALUES (@UserTable, 11, 'Add Attr ','Name', Getdate(), @UserID, 0)
00282: IF @@ERROR <> 0 BEGIN
00283:     ROLLBACK
00284:     RETURN 0
00285: END
00286: INSERT [CIAttr] ([CITypeID] ,[AttrName] ,[AttrTypeID] ,[FieldName] ,[Created] ,[NotForHead] )
00287: VALUES (@UserTable, 'Login' , 10000, 'Login', Getdate(),0)
00288: IF @@ERROR <> 0 BEGIN
00289:     ROLLBACK
00290:     RETURN 0
00291: END
00292: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00293: VALUES (@UserTable, 11, 'Add Attr ','Login', Getdate(), @UserID, 0)
00294: IF @@ERROR <> 0 BEGIN
00295:     ROLLBACK
00296:     RETURN 0
00297: END
00298: INSERT [CIAttr] ([CITypeID] ,[AttrName] ,[AttrTypeID] ,[FieldName] ,[Created] ,[NotForHead] )
00299: VALUES (@UserTable, 'Email' , 10000, 'Email', Getdate(),0)
00300: IF @@ERROR <> 0 BEGIN
00301:     ROLLBACK
00302:     RETURN 0
00303: END
00304: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00305: VALUES (@UserTable, 11, 'Add Attr ','Email', Getdate(), @UserID, 0)
00306: IF @@ERROR <> 0 BEGIN
00307:     ROLLBACK
00308:     RETURN 0
00309: END
00310: INSERT [CIAttr] ([CITypeID] ,[AttrName] ,[AttrTypeID] ,[FieldName] ,[Created] ,[NotForHead] )
00311: VALUES (@UserTable, 'Пароль' , 10000, 'Passwd', Getdate(),0)
00312: IF @@ERROR <> 0 BEGIN
00313:     ROLLBACK
00314:     RETURN 0
00315: END
00316: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00317: VALUES (@UserTable, 11, 'Add Attr ','Passwd', Getdate(), @UserID, 0)
00318: IF @@ERROR <> 0 BEGIN
00319:     ROLLBACK
00320:     RETURN 0
00321: END
00322: INSERT [CIAttr] ([CITypeID] ,[AttrName] ,[AttrTypeID] ,[FieldName] ,[Created] ,[NotForHead] )
00323: VALUES (@RoleTable, 'Code' , 10002, 'Code', Getdate(),0)
00324: IF @@ERROR <> 0 BEGIN
00325:     ROLLBACK
00326:     RETURN 0
00327: END
00328: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00329: VALUES (@RoleTable, 11, 'Add Attr ','Code', Getdate(), @UserID, 0)
00330: IF @@ERROR <> 0 BEGIN
00331:     ROLLBACK
00332:     RETURN 0
00333: END
00334: INSERT [CIAttr] ([CITypeID] ,[AttrName] ,[AttrTypeID] ,[FieldName] ,[Created] ,[NotForHead] )
00335: VALUES (@RoleTable, 'Name' , 10000, 'Name', Getdate(),0)
00336: IF @@ERROR <> 0 BEGIN
00337:     ROLLBACK
00338:     RETURN 0
00339: END
00340: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00341: VALUES (@RoleTable, 11, 'Add Attr ','Name', Getdate(), @UserID, 0)
00342: IF @@ERROR <> 0 BEGIN
00343:     ROLLBACK
00344:     RETURN 0
00345: END
00346: INSERT [CIAttr] ([CITypeID] ,[AttrName] ,[AttrTypeID] ,[FieldName] ,[Created] ,[NotForHead] )
00347: VALUES (@RoleTable, 'Описание' , 10001, 'Description', Getdate(),0)
00348: IF @@ERROR <> 0 BEGIN
00349:     ROLLBACK
00350:     RETURN 0
00351: END
00352: INSERT INTO [CIHistory] ([CITypeID] ,[HistoryTypeID] ,[Description] ,[Comment] ,[Created] ,[CreatorID], CIID)
00353: VALUES (@RoleTable, 11, 'Add Attr ','Описание', Getdate(), @UserID, 0)
00354: IF @@ERROR <> 0 BEGIN
00355:     ROLLBACK
00356:     RETURN 0
00357: END
00358: COMMIT
00359: RETURN @NewID
00360: 
00361: 
00362: 
00363: ALTER procedure [dbo].[CreateNewCI]
00364: @CITypeID int,
00365: @Name nvarchar(50),
00366: @UserID int
00367: as
00368: --declare @CITypeID int,@Name as nvarchar(50),@UserID int
00369: --select  @CITypeID=3, @Name='SQL1',@UserID=1
00370: Declare @CIType_ExtTable nvarchar(100), @Code nvarchar(100), @TmpCode nvarchar(100), @TmpNum int
00371: select top 1  
00372: @CIType_ExtTable=CIType_ExtTable, 
00373: @TmpCode=CIType_CodePrefix + Replicate('0',CIType_ZeroN), 
00374: @TmpNum=CIType_CodeCount  
00375: from KE WHERE CIType_ID=@CITypeID
00376: Select @Code=left(@TmpCode, Len(@TmpCode)-len(cast(@TmpNum as nvarchar)))+cast(@TmpNum as nvarchar) 
00377: Declare @Str1 nvarchar(1000), @Str2 nvarchar(1000), @CIAttr_FieldName nvarchar(100),@CIAttr_Default sql_variant
00378: select  @Str1='INSERT ['+@CIType_ExtTable+'] (Code,Name,CreatorID,Created,ModifierID,Modified,', @Str2= ') VALUES ('''+@Code+''','''+@Name+''','''+cast(@UserID as nvarchar)+''','''+cast(GETDATE() as nvarchar)+''','''+cast(@UserID as nvarchar)+''','''+cast(GETDATE() as nvarchar)+''','
00379: Declare FIELDS CURSOR FOR 
00380: select CIAttr_FieldName,CIAttr_Default from KE WHERE CIType_ID=@CITypeID and CIAttr_AttrName not in ('Code','Name','CreatorID','Created','ModifierID','Modified')
00381: OPEN    FIELDS
00382: FETCH NEXT FROM FIELDS INTO @CIAttr_FieldName, @CIAttr_Default
00383: WHILE @@FETCH_STATUS = 0 BEGIN
00384:     Select @Str1=@Str1+@CIAttr_FieldName+','
00385:     IF (@CIAttr_Default is NULL) select @Str2=@Str2+'NULL,' ELSE select @Str2=@Str2+''''+cast(@CIAttr_Default as nvarchar)+''',' 
00386: FETCH NEXT FROM FIELDS INTO @CIAttr_FieldName, @CIAttr_Default
00387: END
00388: CLOSE FIELDS
00389: DEALLOCATE FIELDS
00390: IF @Str2 is NULL or @Str1 is NULL BEGIN
00391:      select 'Невозможно. Нет смысловых полей.' AS Error, NULL as IID
00392:      IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
00393:      RETURN 2
00394: END
00395: BEGIN TRY
00396: Select @Str1=left(@Str1,len(@Str1)-1)+left(@Str2,len(@Str2)-1)+')' + ' select @ROWIID=scope_Identity()'
00397: END TRY
00398: BEGIN CATCH
00399:      select 'Невозможно. Нет смысловых полей.' AS Error, NULL as IID
00400:      IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
00401:      RETURN 1
00402: END CATCH
00403: --Print @Str1
00404: Declare @IID int, @Err_sp_executesql nvarchar(1000)
00405: BEGIN TRANSACTION
00406: BEGIN TRY
00407:      EXECUTE sp_executesql @Str1,N'@ROWIID int OUTPUT', @ROWIID=@IID OUTPUT
00408: END TRY
00409: BEGIN CATCH
00410:      select ERROR_MESSAGE() AS Error, NULL as IID
00411:      IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
00412:      RETURN 3
00413: END CATCH
00414: UPDATE CIType SET CodeCount=@TmpNum+1 where id=@CITypeID
00415: IF @@TRANCOUNT > 0  COMMIT TRANSACTION
00416: --здесь транзакцию я оборвал, чтобы разрешить создание новой записи БЕЗ отправки уведомления
00417: BEGIN TRY
00418:      INSERT into CIHistory (CITypeID, CIID, [Description], Comment, CreatorID,HistoryTypeID) 
00419:      VALUES (@CITypeID, @IID, 'Created', 'Created. Code: '+ @Code + ', Name: '+ @Name, @UserID,1)
00420: END TRY
00421: BEGIN CATCH
00422:      select ERROR_MESSAGE() AS Error, NULL as IID
00423:      RETURN 4
00424: END CATCH
00425: select NULL as Error, @IID as IID
00426: RETURN 0
00427: 
00428: 
00429: ALTER procedure [dbo].[CreateNewType]
00430: @CustomerCode nvarchar(50),
00431: @ExtTableSuffix nvarchar(20),
00432: @CustomerID int,
00433: @CITypeName nvarchar(50),
00434: @Description ntext = NULL,
00435: @CodePrefix nvarchar(10),
00436: @CodeCount int,
00437: @ZeroN int, 
00438: @Img_Logo nvarchar(50), 
00439: @DicFlag int, 
00440: @UserID int,
00441: @Image32 varbinary(max) =NULL,
00442: @CreateStatusField bit = 0,
00443: @StatusFieldType int = NULL
00444: as
00445: Declare @Str1 nvarchar(1000), @CITypeID int, @CIAttrID1 int, @CIAttrID2 int, @CIAttrID3 int, @CIAttrID4 int, @CIAttrTypeID int,  @CITemplateID int, @AppAccessID int, @CIHistoryID int
00446: BEGIN TRAN
00447: 
00448: If @CreateStatusField=1 
00449: SELECT  @Str1= 'create table ' + 'T_' + @CustomerCode + '_' + @ExtTableSuffix + ' ([ID] int identity (1,1), [Code] nvarchar(20) primary key, [Name] nvarchar(100), [Created] datetime not null default (getdate()), Modified datetime not null default (getdate()), CreatorID int not null, ModifierID int not null, [Image32] [varbinary](max) NULL, StatusID int null)'
00450: else
00451: SELECT  @Str1= 'create table ' + 'T_' + @CustomerCode + '_' + @ExtTableSuffix + ' ([ID] int identity (1,1), [Code] nvarchar(20) primary key, [Name] nvarchar(100), [Created] datetime not null default (getdate()), Modified datetime not null default (getdate()), CreatorID int not null, ModifierID int not null, [Image32] [varbinary](max) NULL)'
00452: 
00453: BEGIN TRY
00454: exec (@Str1)
00455: END TRY
00456: BEGIN CATCH
00457:     ROLLBACK
00458:     select ERROR_MESSAGE() AS Error, 1 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00459:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00460:     RETURN 1
00461: END CATCH
00462: IF @@ERROR <> 0 BEGIN
00463:     ROLLBACK
00464:     select ERROR_MESSAGE() AS Error, 2 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00465:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00466:     RETURN 2
00467: END
00468: --
00469: insert CIType ([CustomerID], [CITypeName], [Description], [ExtTable], [CodePrefix], [CodeCount], [ZeroN], [Img_Logo], [DicFlag], [Image32])
00470: values (@CustomerID, @CITypeName, @Description, 'T_' + @CustomerCode + '_' + @ExtTableSuffix, @CodePrefix, @CodeCount, @ZeroN, @Img_Logo, @DicFlag, @Image32)
00471: IF @@ERROR <> 0 BEGIN
00472:     ROLLBACK
00473:     select ERROR_MESSAGE() AS Error, 3 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00474:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00475:     RETURN 3
00476: END
00477: select @CITypeID = scope_identity()
00478: --
00479: insert into CIAttr (CITypeID, AttrName, AttrTypeID, FieldName) values (@CITypeID,'Code', 10002, 'Code')
00480: IF @@ERROR <> 0 BEGIN
00481:     ROLLBACK
00482:     select ERROR_MESSAGE() AS Error,4 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00483:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00484:     RETURN 4
00485: END
00486: select @CIAttrID1 = scope_identity()
00487: --
00488: insert into CIAttr (CITypeID, AttrName, AttrTypeID, FieldName) values (@CITypeID,'Name', 10000, 'Name')
00489: IF @@ERROR <> 0 BEGIN
00490:     ROLLBACK
00491:     select ERROR_MESSAGE() AS Error, 5 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00492:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00493:     RETURN 5
00494: END
00495: select @CIAttrID2 = scope_identity()
00496: --
00497: If @CreateStatusField=1 BEGIN
00498: select @CIAttrID3=0
00499: insert into CIAttr (CITypeID, AttrName, AttrTypeID, FieldName) values (@CITypeID,'Status', @StatusFieldType, 'StatusID')
00500: IF @@ERROR <> 0 BEGIN
00501:     ROLLBACK
00502:     select ERROR_MESSAGE() AS Error,6 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00503:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00504:     RETURN 6
00505: END
00506: select @CIAttrID3 = scope_identity()
00507: END
00508: --
00509: --insert into CIAttr (CITypeID, AttrName, AttrTypeID, FieldName) values (@CITypeID,'Image32', 10005, 'Image32')
00510: --IF @@ERROR <> 0 BEGIN
00511: --    ROLLBACK
00512: --    select ERROR_MESSAGE() AS Error, 7 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00513: --    NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00514: --    RETURN 7
00515: --END
00516: --select @CIAttrID4 = scope_identity()
00517: -- Теперь создаем доступ для админов данного кастомера
00518: declare @AdminRoleID int
00519: select @AdminRoleID=AdminRoleID from Customer where ID=@CustomerID
00520: IF @AdminRoleID=NULL BEGIN
00521:     ROLLBACK
00522:     select 'Нет роли админа в табле Customer' AS Error, 8 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00523:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00524:     RETURN 8
00525: END
00526: -- Добавляем новый тип в список типов
00527: insert CIAttrType (ID, CustomerID, AttrTypeName, SQLTypeName) 
00528: values (@CITypeID, @CustomerID, @CITypeName, 'integer')
00529: IF @@ERROR <> 0 BEGIN
00530:     ROLLBACK
00531:     select ERROR_MESSAGE() AS Error, 9 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00532:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00533:     RETURN 9
00534: END
00535: select @CIAttrTypeID = scope_identity()
00536: --
00537: DECLARE @ListTemplate nvarchar(1000), @CardTemplate nvarchar(1000)
00538: select @ListTemplate=template from Defaults where [key]='User_ListTemplate'
00539: IF @ListTemplate=NULL BEGIN
00540:     ROLLBACK
00541:     select 'Нет шаблона User_ListTemplate в табле Defaults' AS Error,10 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00542:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00543:     RETURN 10
00544: END
00545: select @CardTemplate=template from Defaults where [key]='User_CardTemplate'
00546: IF @CardTemplate=NULL BEGIN
00547:     ROLLBACK
00548:     select 'Нет шаблона User_CardTemplate в табле Defaults' AS Error,11 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00549:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00550:     RETURN 11
00551: END
00552: insert CITemplate (CITypeID, RoleID, TemplateName, ListTemplate, CardTemplate, DefaultFlag,CommonFlag) 
00553: values (@CITypeID, @AdminRoleID, 'Standard', @ListTemplate,@CardTemplate,1,1)
00554: IF @@ERROR <> 0 BEGIN
00555:     ROLLBACK
00556:     select ERROR_MESSAGE() AS Error,12 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00557:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00558:     RETURN 12
00559: END
00560: select @CITemplateID = scope_identity()
00561: --
00562: insert  AppAccess (CustomerID, LevelCode, RoleID, AccessCode, CITypeID, CIAttrID, CIID) 
00563: values (@CustomerID, 'TYPE', @AdminRoleID, 'SUID', @CITypeID, 0, 0)
00564: IF @@ERROR <> 0 BEGIN
00565:     ROLLBACK
00566:     select ERROR_MESSAGE() AS Error, 13 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00567:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00568:     RETURN 13
00569: END
00570: select @AppAccessID = scope_identity()
00571: --
00572: INSERT [CIHistory]([CITypeID],[CIID] ,[Description] ,[Comment],[Created],[CreatorID],[HistoryTypeID])
00573: VALUES(@CITypeID, 0, 'Created', 'CITypeName=''' + @CITypeName + ''',CustomerCode=''' + @CustomerCode + ''',ExtTableName=''' + 'T_' + @CustomerCode + '_' + @ExtTableSuffix +
00574: ''',CITypeID=' + cast(@CITypeID as nvarchar) + ',CIAttrID1=' + cast(@CIAttrID1 as nvarchar) + ',CIAttrID2=' + cast(@CIAttrID2 as nvarchar)  + ',CIAttrID3=' + cast(@CIAttrID3 as nvarchar) + 
00575: ',AdminRoleID=' + cast(@AdminRoleID as nvarchar)+ ',CITemplateID=' + cast(@CITemplateID as nvarchar) + ',AppAccessID=' + cast(@AppAccessID as nvarchar)+ 
00576: ',CodePrefix=''' + @CodePrefix + ''',Img_Logo=''' + @Img_Logo + ''',User_ListTemplate=''' + @ListTemplate + ''',User_CardTemplate=''' + @CardTemplate + '',
00577: Getdate(),@UserID,11)
00578: IF @@ERROR <> 0 BEGIN
00579:     ROLLBACK
00580:     select ERROR_MESSAGE() AS Error, 14 as ErrorCode, NULL as ExtTableName, NULL as CITypeID, NULL as CIAttrID1, NULL as CIAttrID2, NULL as AdminRoleID,
00581:     NULL as User_ListTemplate, NULL as User_CardTemplate, NULL as CITemplateID, NULL as AppAccessID, NULL as CIHistoryID
00582:     RETURN 14
00583: END
00584: select @CIHistoryID = scope_identity()
00585: 
00586: COMMIT TRAN
00587: select NULL as Error,  0 as ErrorCode, 'T_' + @CustomerCode + '_' + @ExtTableSuffix as ExtTableName, 
00588: @CITypeID as CITypeID, @CIAttrID1 as CIAttrID1, @CIAttrID2 as CIAttrID2, @CIAttrID3 as CIAttrID3 ,@AdminRoleID as AdminRoleID,
00589: @ListTemplate as User_ListTemplate, @CardTemplate as User_CardTemplate,@CIAttrTypeID as CIAttrTypeID,
00590: @CITemplateID as CITemplateID, @AppAccessID as AppAccessID, @CIHistoryID as CIHistoryID
00591: Return 0

Comments ( )