Title / Description
Code SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Version 1.1 -- ============================================= -- Author: Pavel Lastovka -- Create date: 2007-07-05 -- Modification date: 2010-02-17 15:15 -- Description: Update type of curves and variables -- in local database from the server -- ============================================= ALTER Procedure [object].[usp_update_export_types] @lIdTypeProductionLine As UniqueIdentifier = Null, @lLoggingLevel As TinyInt = Null, @lUserId As UniqueIdentifier = Null Output As Begin Set NoCount On -- Default variables Declare @lProcName As NVarChar(128) Declare @lExecStr As NVarChar(MAX) Set @lProcName = N'[object].[usp_update_export_types]' -- Retrieve the user id If @lUserId Is Null Begin Begin Try Select Top 1 @lUserId = [id_user] From [core].[user] Where [identifier] = N'system' End Try Begin Catch Print N'Exp: Cannot obtain a system user - ' + ERROR_MESSAGE() Return -1 End Catch If @lUserId Is Null Begin Print N'Err: Cannot obtain a system user - the account doesn''t exist in the system' Return -1 End End -- Obtain parameters If @lLoggingLevel Is Null Begin Begin Try Exec [core].[csp_get_param] @lName = N'LoggingLevel', @lGroupName = N'Local', @lDefaultValue = N'2', @lValue = @lExecStr Output, @lDataType = N'TinyInt', @lUserId = @lUserId Set @lLoggingLevel = Cast (@lExecStr As TinyInt) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = 2, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot retrieve parameter LoggingLevel', @lUserId = @lUserId Set @lLoggingLevel = 2 End Catch End -- Updating users Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName, @lDescription = N'Updating users', @lUserId = @lUserId Begin Try Delete From [core].[user] Where [id_user] Not In ( Select [id_core_user] From [MIXCS].[dbo].[core_user] ) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot delete users', @lUserId = @lUserId End Catch Begin Try Update [core].[user] Set [identifier] = [mixcs_user].[identifier], [full_name] = [mixcs_user].[full_name], [password] = [mixcs_user].[password], [email] = [mixcs_user].[email], [x_user] = [mixcs_user].[x_user], [x_created] = [mixcs_user].[x_created], [x_modified] = GetUTCDate() From [core].[user] As [mixit_user] Inner Join [MIXCS].[dbo].[core_user] As [mixcs_user] On [mixit_user].[id_user] = [mixcs_user].[id_core_user] End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot update users', @lUserId = @lUserId End Catch Begin Try Insert Into [core].[user] ( [id_user], [identifier], [full_name], [password], [email], [x_user], [x_created], [x_modified] ) Select [id_core_user] As [id_user], [identifier] As [identifier], [full_name] As [full_name], [password] As [password], [email] As [email], [x_user] As [x_user], [x_created] As [x_created], GetUTCDate() As [x_modified] From [MIXCS].[dbo].[core_user] Where [id_core_user] Not In ( Select [id_user] From [core].[user] ) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot insert a new user into the database', @lUserId = @lUserId End Catch Begin Try If Not Exists(Select [id_user] From [core].[user] Where [id_user] = @lUserId) Begin Select Top 1 @lUserId = [id_user] From [core].[user] Where [identifier] = N'system' End End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot update an user id', @lUserId = @lUserId End Catch -- Update curve types Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName, @lDescription = N'Updating curve types', @lUserId = @lUserId Begin Try Set @lExecStr = N'Delete From [type].[type_curve] Where' + Case When @lIdTypeProductionLine Is Not Null Then N' [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''' And ' Else N' ' End + N'[id_type_curve] Not In ( Select [id_type_curve] From [MIXCS].[dbo].[type_curve]' + Case When @lIdTypeProductionLine Is Not Null Then N' Where [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''' Else N'' End + N' )' Exec(@lExecStr) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot delete curve types', @lUserId = @lUserId End Catch -- Update variable types Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName, @lDescription = N'Updating variable types', @lUserId = @lUserId Begin Try Set @lExecStr = N'Delete From [type].[type_variable] Where' + Case When @lIdTypeProductionLine Is Not Null Then N' [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''' And ' Else N' ' End + N'[id_type_variable] Not In ( Select [id_type_variable] From [MIXCS].[dbo].[type_variable]' + Case When @lIdTypeProductionLine Is Not Null Then N' Where [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''' Else N'' End + N' )' Exec(@lExecStr) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot delete variable types', @lUserId = @lUserId End Catch -- Update production lines Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName, @lDescription = N'Updating production line types', @lUserId = @lUserId Begin Try Set @lExecStr = N'Delete From [type].[type_production_line] Where [id_type_production_line] Not In ( Select [id_type_production_line] From [MIXCS].[dbo].[type_production_line] ' + Case When @lIdTypeProductionLine Is Not Null Then N' Where [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''' ) And [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''' Else N' )' End Exec(@lExecStr) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot delete production line types', @lUserId = @lUserId End Catch Begin Try Set @lExecStr = N'Update [type].[type_production_line] Set [identifier] = [mixcs_line].[identifier], [identification] = [mixcs_line].[identification], [x_user] = N''' + Cast(@lUserId As NVarChar(64)) + N''', [x_modified] = GetUTCDate() From [type].[type_production_line] As [mixit_line] Inner Join [MIXCS].[dbo].[type_production_line] As [mixcs_line] On [mixcs_line].[id_type_production_line] = [mixit_line].[id_type_production_line]' + Case When @lIdTypeProductionLine Is Not Null Then N' And [mixcs_line].[id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''' Else N'' End Exec(@lExecStr) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot update production line types', @lUserId = @lUserId End Catch Begin Try Set @lExecStr = N'Insert Into [type].[type_production_line] ( [id_type_production_line], [identifier], [identification], [x_user], [x_created] ) Select [id_type_production_line] As [id_type_production_line], [identifier] As [identifier], [identification] As [identification], N''' + Cast(@lUserId As NVarChar(64)) + N''' As [x_user], [x_created] As [x_created] From [MIXCS].[dbo].[type_production_line] Where [id_type_production_line] Not In ( Select [id_type_production_line] From [type].[type_production_line]' + Case When @lIdTypeProductionLine Is Not Null Then N' Where [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''' ) And [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''' Else N' )' End Exec(@lExecStr) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot insert production line types', @lUserId = @lUserId End Catch -- Update curve types Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName, @lDescription = N'Updating curve types', @lUserId = @lUserId Begin Try Set @lExecStr = N'Update [type].[type_curve] Set [tag_id] = [mixcs_curve].[tag_index], [identifier] = [mixcs_curve].[identifier], [scale_factor] = [mixcs_curve].[scale_factor], [id_type_production_line] = [mixcs_curve].[id_type_production_line], [x_user] = N''' + Cast(@lUserId As NVarChar(64)) + N''', [x_modified] = GetUTCDate() From [type].[type_curve] As [mixit_curve] Inner Join [MIXCS].[dbo].[type_curve] As [mixcs_curve] On [mixcs_curve].[id_type_curve] = [mixit_curve].[id_type_curve]' + Case When @lIdTypeProductionLine Is Not Null Then N' And [mixit_curve].[id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''' Else N'' End Exec(@lExecStr) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot update curve types', @lUserId = @lUserId End Catch Begin Try Set @lExecStr = N'Insert Into [type].[type_curve] ( [id_type_curve], [id_type_production_line], [tag_id], [identifier], [scale_factor], [x_user], [x_created] ) Select [id_type_curve] As [id_type_curve], [id_type_production_line] As [id_type_production_line], [tag_index] As [tag_id], [identifier] As [identifier], [scale_factor] As [scale_factor], N''' + Cast(@lUserId As NVarChar(64)) + N''' As [x_user], [x_created] As [x_created] From [MIXCS].[dbo].[type_curve] Where [id_type_curve] Not In ( Select [id_type_curve] From [type].[type_curve]' + Case When @lIdTypeProductionLine Is Not Null Then N' Where [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''' ) And [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''' Else N' )' End Exec(@lExecStr) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot insert curve types', @lUserId = @lUserId End Catch -- Update variable types Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName, @lDescription = N'Updating variable types', @lUserId = @lUserId Begin Try Set @lExecStr = N'Update [type].[type_variable] Set [tag_id] = [mixcs_variable].[tag_index], [identifier] = [mixcs_variable].[identifier], [scale_factor] = [mixcs_variable].[scale_factor], [id_type_production_line] = [mixcs_variable].[id_type_production_line], [coding_system] = [mixcs_variable].[group], [x_user] = N''' + Cast(@lUserId As NVarChar(64)) + N''', [x_modified] = GetUTCDate() From [type].[type_variable] As [mixit_variable] Inner Join [MIXCS].[dbo].[type_variable] As [mixcs_variable] On [mixcs_variable].[id_type_variable] = [mixit_variable].[id_type_variable]' + Case When @lIdTypeProductionLine Is Not Null Then N' And [mixcs_variable].[id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''' Else N'' End Exec(@lExecStr) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot update variable types', @lUserId = @lUserId End Catch Begin Try Set @lExecStr = N'Insert Into [type].[type_variable] ( [id_type_variable], [id_type_production_line], [tag_id], [identifier], [scale_factor], [coding_system], [x_user], [x_created] ) Select [id_type_variable] As [id_type_variable], [id_type_production_line] As [id_type_production_line], [tag_index] As [tag_id], [identifier] As [identifier], [scale_factor] As [scale_factor], [group] As [coding_system], N''' + Cast(@lUserId As NVarChar(64)) + N''' As [x_user], [x_created] As [x_created] From [MIXCS].[dbo].[type_variable] Where [id_type_variable] Not In ( Select [id_type_variable] From [type].[type_variable]' + Case When @lIdTypeProductionLine Is Not Null Then N' Where [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''' ) And [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''' Else N' )' End Exec(@lExecStr) End Try Begin Catch Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName, @lDescription = N'Cannot insert variable types', @lUserId = @lUserId End Catch Return 1 End
Author
Highlight as C C++ CSS Clojure Delphi ERb Groovy (beta) HAML HTML JSON Java JavaScript PHP Plain text Python Ruby SQL XML YAML diff code
1.1.2
1.1.2