Last active
April 30, 2019 09:53
-
-
Save adnanzameer/107d18f1ffaed6d3c5c7fa23d2b5d668 to your computer and use it in GitHub Desktop.
SQL script to change page branch master language in Episerver
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AZChangePageBranchMasterLanguage]') AND type in (N'P', N'PC')) | |
| DROP PROCEDURE [dbo].[AZChangePageBranchMasterLanguage] | |
| SET ANSI_NULLS ON | |
| GO | |
| SET QUOTED_IDENTIFIER ON | |
| GO | |
| CREATE PROCEDURE [dbo].[AZChangePageBranchMasterLanguage] | |
| @content_id int, | |
| @language_branch varchar(20), | |
| @recursive bit, | |
| @switch_only bit | |
| AS | |
| DECLARE @language_branch_id nchar(17); | |
| DECLARE @language_branch_nid int; | |
| DECLARE @prev_language_branch_nid int; | |
| DECLARE @child_Id int; | |
| DECLARE @Fetch int; | |
| DECLARE @target_lang_version_exist int; | |
| SET @language_branch_nid = (SELECT pkID FROM tblLanguageBranch WHERE (LanguageID = @language_branch)) | |
| SET @language_branch_id = (SELECT LanguageID FROM tblLanguageBranch WHERE (LanguageID = @language_branch)) | |
| SET @prev_language_branch_nid = (SELECT fkMasterLanguageBranchID FROM tblContent WHERE pkID = @content_id) | |
| SET @target_lang_version_exist = (SELECT count(*) FROM tblContentLanguage WHERE (fkContentID = @content_id AND fkLanguageBranchID = @language_branch_nid)) | |
| -- for switch only check if the page exist in the selected language and only then switch | |
| IF 1 = @switch_only | |
| BEGIN | |
| print 'target_lang_version_exist' + str(@target_lang_version_exist); | |
| IF @target_lang_version_exist > 0 | |
| BEGIN | |
| UPDATE tblContent | |
| SET fkMasterLanguageBranchID = @language_branch_nid | |
| WHERE pkID = @content_id AND fkMasterLanguageBranchID = @prev_language_branch_nid | |
| END | |
| ELSE | |
| BEGIN | |
| RAISERROR | |
| (N'The Selected page with ID:%d, cannot switch master branch since there is no version in the selected target language: %s.', | |
| 11, 1, @content_id, @language_branch); | |
| END | |
| END | |
| ELSE | |
| BEGIN | |
| IF @target_lang_version_exist > 0 | |
| BEGIN | |
| RAISERROR | |
| (N'The Selected page with ID:%d, cannot be translated since there already is a version in the selected target language: %s.', | |
| 11, 1, @content_id, @language_branch); | |
| END | |
| ELSE | |
| BEGIN | |
| UPDATE tblContent | |
| SET fkMasterLanguageBranchID = @language_branch_nid | |
| WHERE pkID = @content_id | |
| UPDATE tblContentProperty | |
| SET fkLanguageBranchID = @language_branch_nid | |
| WHERE fkContentID = @content_id AND fkLanguageBranchID = @prev_language_branch_nid | |
| UPDATE tblContentLanguage | |
| SET fkLanguageBranchID = @language_branch_nid | |
| WHERE fkContentID = @content_id AND fkLanguageBranchID = @prev_language_branch_nid | |
| UPDATE tblWorkContent | |
| SET fkLanguageBranchID = @language_branch_nid | |
| WHERE fkContentID = @content_id AND fkLanguageBranchID = @prev_language_branch_nid | |
| END | |
| END | |
| IF 1 = @recursive | |
| BEGIN | |
| DECLARE children_cursor CURSOR LOCAL FOR | |
| select pkID from tblContent where fkParentID = @content_id | |
| OPEN children_cursor | |
| FETCH NEXT FROM children_cursor INTO @child_Id | |
| SET @Fetch=@@FETCH_STATUS | |
| WHILE @Fetch = 0 | |
| BEGIN | |
| print @child_id | |
| print @language_branch_id | |
| exec [dbo].[AZChangePageBranchMasterLanguage] @child_id, @language_branch_id, @recursive, @switch_only | |
| FETCH NEXT FROM children_cursor INTO @child_Id | |
| SET @Fetch=@@FETCH_STATUS | |
| END | |
| CLOSE children_cursor | |
| DEALLOCATE children_cursor | |
| END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment