Funct, SP SQL

BEGIN TRANSACTION [Tran1]
	BEGIN TRY
	
		EXEC('RAISERROR ( ''Create Table Currencies'', 10,1) WITH NOWAIT');
		EXEC('
				IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[Currencies]'') AND type in (N''U''))
				BEGIN
					CREATE TABLE [dbo].[Currencies]
					(
						[Id] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,
						[Name] [NVARCHAR](255) NOT NULL,
						[Code] [VARCHAR](255) NOT NULL,
						[CreatorId] [INT] NOT NULL,
	                    [CreationDateUtc] [DATETIME] NOT NULL DEFAULT(GETUTCDATE()),
	                    [LastModifierId] [INT] NOT NULL,
	                    [LastModificationDateUtc] [DATETIME] NOT NULL DEFAULT(GETUTCDATE())
					)
				END
		');  

		EXEC('RAISERROR ( ''Create Table CurrenciesTranslations'', 10,1) WITH NOWAIT');
		EXEC('
				IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[CurrenciesTranslations]'') AND type in (N''U''))
				BEGIN
					CREATE TABLE [dbo].[CurrenciesTranslations]
					(
						[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	                    [CurrencyId] [int] NOT NULL,
	                    [LanguageId] [int] NOT NULL,
	                    [Name] [NVARCHAR](255) NOT NULL,
						[CreatorId] [INT] NOT NULL,
	                    [CreationDateUtc] [DATETIME] NOT NULL DEFAULT(GETUTCDATE()),
	                    [LastModifierId] [INT] NOT NULL,
	                    [LastModificationDateUtc] [DATETIME] NOT NULL DEFAULT(GETUTCDATE()),
						CONSTRAINT FK_CurrenciesTranslations_Languages_LanguageId FOREIGN KEY (LanguageId) REFERENCES Languages(Id),
						CONSTRAINT FK_CurrenciesTranslations_Currencies_CurrencyId FOREIGN KEY (CurrencyId) REFERENCES Currencies(Id) ON DELETE CASCADE
					)
				END
		');	

		EXEC('RAISERROR ( ''Add Format  Column in ProductTypes Table '', 10,1) WITH NOWAIT');
		EXEC('
				IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N''Format'' AND Object_ID = Object_ID(N''ProductTypes''))    
				BEGIN
					ALTER TABLE [ProductTypes] 
					ADD Format NVARCHAR(MAX) NULL
				END
		');

		EXEC('RAISERROR ( ''Creating/Updating [dbo].[ImageType]'', 10,1) WITH NOWAIT');
		IF TYPE_ID(N'ImageType') IS NULL
		EXEC('
				CREATE TYPE [dbo].[ImageType] AS TABLE
				(
					[Id] [INT],
					[Alt] [NVARCHAR](255),
					[FileName] [NVARCHAR](255),
					[CopyRights] [NVARCHAR](255),
					[ORDER] INT
				)
		');

		EXEC('RAISERROR ( ''Creating/Updating [dbo].[Split]'', 10,1) WITH NOWAIT');
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Split') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
			EXEC('CREATE FUNCTION [dbo].Split(@String varchar(8000), @Delimiter char(1)) RETURNS @temptable TABLE(items varchar(8000)) AS BEGIN INSERT INTO @temptable(items) SELECT ''''; RETURN; END');
		EXEC('
				ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))     
					returns @temptable TABLE (items varchar(8000))
				as     
				begin
					set @String = RTRIM(LTRIM(@String))
					declare @idx int     
					declare @slice varchar(8000)     
				    
					select @idx = 1     
						if len(@String)<1 or @String is null  return     
				    
					while @idx!= 0     
					begin     
						set @idx = charindex(@Delimiter,@String)     
						if @idx!=0     
							set @slice = left(@String,@idx - 1)     
						else     
							set @slice = @String     
						
						if(len(@slice)>0)
							insert into @temptable(items) values(@slice)     

						set @String = right(@String,len(@String) - @idx)     
						if len(@String) = 0 break     
					end 
				return     
				end
		');

		EXEC('RAISERROR ( ''Creating/Updating [dbo].[GetAllAttributeValues]'', 10,1) WITH NOWAIT');
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'GetAllAttributeValues') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
			EXEC('CREATE FUNCTION [dbo].[GetAllAttributeValues]() RETURNS INT AS BEGIN RETURN 1; END');
		EXEC('
				ALTER FUNCTION [dbo].[GetAllAttributeValues] 
                (
                    @AttributeId INT
					,@BaseProductId INT
                )
                RETURNS NVARCHAR(MAX)
                AS
                BEGIN
					DECLARE @Seperator varchar(10) ='' \n '';
					DECLARE @Values nvarchar(MAX) = @Seperator;

					SELECT @Values = CASE WHEN @Values = @Seperator THEN '''' ELSE @Values END  + text + @Seperator
					FROM	ProductsAttributesValues WHERE BaseProductId = @BaseProductId AND AttributeId = @AttributeId
						
					SET @Values = LEFT(@Values, LEN(@Values) - LEN(@Seperator))

					RETURN @Values 
                END
		');

		EXEC('RAISERROR ( ''Creating/Updating [dbo].[GetAllProductAttributeValuesByGroupAsXml]'', 10,1) WITH NOWAIT');
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'GetAllProductAttributeValuesByGroupAsXml') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
			EXEC('CREATE FUNCTION [dbo].[GetAllProductAttributeValuesByGroupAsXml]() RETURNS INT AS BEGIN RETURN 1; END');
		EXEC('
				ALTER FUNCTION [dbo].[GetAllProductAttributeValuesByGroupAsXml] 
				(
					@GroupId INT, 
					@BaseProductId INT

				)
				RETURNS XML
				AS 
				BEGIN
					DECLARE @Result XML;
					SELECT @Result = (	
											SELECT    
											    A.Id AS AttributeId, 
												A.Name AS AttributeName,
												A.[Key] AS KeyAttribute,
												A.KeyAttributeOrder, 
												A.[Order],
												dbo.GetAllAttributeValues(A.Id, @BaseProductId) AS AttributeValue
										FROM  ProductsAttributesValues PAV
												INNER JOIN Attributes A ON (PAV.AttributeId = A.Id)
												INNER JOIN AttributesGroups AG ON (A.AttributeGroupId = AG.id)
										WHERE A.AttributeGroupId = @GroupId 
										AND   PAV.BaseProductId = @BaseProductId

										FOR XML AUTO, ELEMENTS, ROOT(''Attributes'')
									)
					RETURN @Result;
				END
		');

		EXEC('RAISERROR ( ''Creating/Updating [dbo].[GetMinimumProductPrice]'', 10,1) WITH NOWAIT');
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'GetMinimumProductPrice') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
			EXEC('CREATE FUNCTION [dbo].GetMinimumProductPrice(@String varchar(8000), @Delimiter char(1)) RETURNS @temptable TABLE(items varchar(8000)) AS BEGIN INSERT INTO @temptable(items) SELECT ''''; RETURN; END');
		EXEC('
				ALTER FUNCTION [dbo].[GetMinimumProductPrice] 
				(
					@BaseProductId INT
					,@CountryId INT
					,@MerchnatId INT = -1
				)
				RETURNS @t TABLE
				(
					TotalMecrhants INT
					,MinimumPrice FLOAT
					,Id INT
				)
				AS
				BEGIN

					INSERT INTO @t 
					SELECT	COUNT(*)
							,CASE WHEN ISNULL(MIN(COALESCE(MP.NewPrice, MP.Price, 2147483647)),0) = 2147483647 THEN 0 ELSE ISNULL(MIN(COALESCE(MP.NewPrice, MP.Price, 2147483647)), 0) END
							,@BaseProductId
					FROM	MerchantsProducts MP
							INNER JOIN Merchants M
								ON MP.MerchantId = M.Id
					WHERE   (@MerchnatId = -1 OR MP.MerchantId = @MerchnatId)
							AND (MP.BaseProductId= @BaseProductId)
							AND (M.CountryId = @CountryId)
							AND (MP.Visible = 1)
							AND (M.Visible = 1)
							AND (MP.InventoryControlType = 1 OR (MP.InventoryControlType = 3 AND MP.Inventory IS NOT NULL AND  MP.Inventory <> 0))
					RETURN 
				END
		');

		EXEC('RAISERROR ( ''Creating/Updating [dbo].[GetAllPopTemplates]'', 10,1) WITH NOWAIT');
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'GetAllPopTemplates')
			EXEC('CREATE PROCEDURE [dbo].[GetAllPopTemplates] AS BEGIN SET NOCOUNT ON; END');
		EXEC('
				ALTER PROCEDURE [dbo].[GetAllPopTemplates]
				(
					@PageIndex INT
					,@PageSize INT
					,@SortExpression VARCHAR(50)
					,@MerchantId INT
				)
				AS
				BEGIN
					
					;WITH CTEPage AS
					(
						SELECT	TOP(@PageSize * @PageIndex)
								PT.[Id] AS [ID]
								,PT.[Name] AS [Name]
								,PT.[Description] AS [Description]
								,PT.[MerchantId] AS [MerchantId]
								,PT.[CreationDateUtc] AS CreationDateUtc
								,M.Name AS [MerchantName]
								,ROW_NUMBER() OVER 
								(
									ORDER BY 
									    CASE WHEN @SortExpression = ''Id'' THEN PT.Id END ASC,
										CASE WHEN @SortExpression = ''Id DESC'' THEN PT.Id END DESC,
										CASE WHEN @SortExpression = ''Name'' THEN PT.Name END ASC,
										CASE WHEN @SortExpression = ''Name DESC'' THEN PT.Name END DESC,
										CASE WHEN @SortExpression = ''Description'' THEN PT.Description END ASC,
										CASE WHEN @SortExpression = ''Description DESC'' THEN PT.Description END DESC,
										CASE WHEN @SortExpression = ''MerchantName'' THEN M.Name END ASC,
										CASE WHEN @SortExpression = ''MerchantName DESC'' THEN M.Name END DESC,
										PT.CreationDateUtc DESC
								) AS [ROW_Number]										
						FROM	[dbo].[PopTemplates] AS PT
								INNER JOIN Merchants M
									ON (M.Id = PT.MerchantId)
                        WHERE	(@MerchantId = -1 OR PT.MerchantId = @MerchantId)

					)
					SELECT	TOP(@PageSize)
							PT.*
					FROM	CTEPage AS PT 
					WHERE	PT.[ROW_Number] > (@PageIndex - 1) * @PageSize
					ORDER BY PT.ROW_Number ASC;
				END
		');	

		IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'InsertOrUpdateMerchantProduct')
			EXEC('CREATE PROCEDURE [dbo].[InsertOrUpdateMerchantProduct] AS BEGIN SET NOCOUNT ON; END');
		EXEC('
				ALTER PROCEDURE [dbo].[InsertOrUpdateMerchantProduct] 
				(
						@BaseProductId int
						,@MerchantProductId int
						,@MerchantId int
						,@UserId int
						,@MerchantProductName nvarchar(225)
						,@Price float
						,@NewPrice float
						,@SKU nvarchar(255)
						,@Offer nvarchar(255)
						,@OfferStartDate datetime
						,@OfferEndDate datetime
						,@Field1 nvarchar(1024)
						,@Field2 nvarchar(1024)
						,@Field3 nvarchar(1024)
						,@Field4 nvarchar(1024)
						,@Visible bit
						,@Featured bit
						,@InventoryControlType int
						,@Inventory int
						,@InsertedId int OUTPUT
				)
				AS
				BEGIN
					SET NOCOUNT ON;
					DECLARE @TranCount INT;
					SET @TranCount = @@TRANCOUNT;

					BEGIN TRY
						IF @TranCount = 0
							BEGIN TRANSACTION
						ELSE
							SAVE TRANSACTION InsertOrUpdateMerchantProduct;

						DECLARE  @MerchantProduct TABLE
						(
							Action VARCHAR(50)
							,Id INT
						)

						DECLARE @CountryId INT;
						SELECT	@CountryId = M.CountryId 
						FROM	Merchants M 
								INNER JOIN Countries C 
									ON C.Id = M.CountryId 
								INNER JOIN Currencies CU 
									ON CU.Id = C.CurrencyId 
						WHERE	M.Id = @MerchantId;

						MERGE MerchantsProducts AS MP
						USING (SELECT @BaseProductId AS BaseProductId, @MerchantProductId AS MerchantProductId, @MerchantId AS MerchantId) AS S
							ON S.BaseProductId = MP.BaseProductId AND MP.Id = @MerchantProductId AND MP.MerchantId = S.MerchantId
						WHEN NOT MATCHED THEN
							INSERT (BaseProductId, MerchantId, SKU, Offer,OfferStartDateUtc, OfferEndDateUtc, Field1, Field2, Field3, Field4,Name, Price, NewPrice, InventoryControlType, Inventory, Featured, Visible, CreatorId, LastModifierId)
							VALUES(@BaseProductId, @MerchantId, @SKU, @Offer, @OfferStartDate, @OfferEndDate , @Field1, @Field2, @Field3, @Field4, @MerchantProductName ,@Price, @NewPrice ,@InventoryControlType, @Inventory, @Featured, @Visible, @UserId, @UserId)
						WHEN MATCHED THEN 
							UPDATE 
								SET	SKU = @SKU
									,Price = @Price
									,Name = @MerchantProductName
									,InventoryControlType = @InventoryControlType
									,Inventory = @Inventory
									,NewPrice = @NewPrice
									,Offer = @Offer
									,OfferStartDateUtc = @OfferStartDate
									,OfferEndDateUtc =  @OfferEndDate
									,Field1 = @Field1
									,Field2 = @Field2
									,Field3 = @Field3
									,Field4 = @Field4
									,Visible = @Visible
									,Featured = @Featured
									,LastModifierId = @UserId
									,LastModificationDateUtc = GETUTCDATE()
						OUTPUT $action, inserted.Id INTO @MerchantProduct;

						SELECT @InsertedId = Id FROM @MerchantProduct;
						RETURN @InsertedId;

			LBEXIT:
						IF @TranCount = 0
							COMMIT;
						END TRY
					BEGIN CATCH
						DECLARE @Error INT, @Message VARCHAR(4000), @XState INT;
						SELECT	@Error = ERROR_NUMBER() ,@Message = ERROR_MESSAGE() ,@XState = XACT_STATE();

						IF @XState = -1
							ROLLBACK;
						IF @XState = 1 AND @TranCount = 0
							rollback
						IF @XState = 1 AND @TranCount > 0
							ROLLBACK TRANSACTION  InsertOrUpdateMerchantProduct;
						RAISERROR (''InsertOrUpdateMerchantProduct: %d: %s'', 16, 1, @error, @message) ;
					END CATCH
				END
		');	

		EXEC('RAISERROR ( ''Creating/Updating [dbo].[GetAllMerchants]'', 10,1) WITH NOWAIT');
			IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'GetAllMerchants')
		EXEC('CREATE PROCEDURE [dbo].[GetAllMerchants] AS BEGIN SET NOCOUNT ON; END');
		EXEC('
				ALTER PROCEDURE [dbo].[UpdateAttributeOrder] 
				(
					@AttributeId INT
					,@GroupId INT
					,@UserId INT
					,@Action VARCHAR(10)
				)
				AS
				BEGIN
					
					SET NOCOUNT ON;
					DECLARE @TranCount INT;
					SET @TranCount = @@TRANCOUNT;

					BEGIN TRY
						IF @TranCount = 0
							BEGIN TRANSACTION
						ELSE
							SAVE TRANSACTION UpdateAttributeOrder;

						DECLARE @OldOrder INT;
						DECLARE @NewOrder INT;

						SELECT @OldOrder = A.[Order] FROM Attributes A WHERE A.Id = @AttributeId AND A.AttributeGroupId = @GroupId;
						
						IF(@Action = ''up'')
						BEGIN
							SET @NewOrder = @OldOrder - 1;
						END
						ELSE
						BEGIN
							SET @NewOrder = @OldOrder + 1;
						END
						
						UPDATE	Attributes
						SET		[Order] = (CASE 
												WHEN 
													[Order] = @OldOrder
												THEN 
													@NewOrder
												WHEN 
													@NewOrder > @OldOrder 
												THEN 
													[Order] - 1
												ELSE 
													[Order] + 1 
											END)
								,[LastModifierId] = @UserID
								,[LastModificationDateUtc] = GETUTCDATE()
						WHERE	AttributeGroupId = @GroupId
								AND (([Order] BETWEEN @OldOrder AND @NewOrder) OR ([Order] BETWEEN @NewOrder AND @OldOrder));

						;WITH CTE AS
						(
							SELECT A.Id, ROW_NUMBER() OVER(ORDER BY [A].[Order]) AS NewOrder FROM Attributes A WHERE A.AttributeGroupId = @GroupId
						)
						UPDATE A 
						SET A.[Order] = CTE.NewOrder
						FROM Attributes AS A INNER JOIN CTE ON CTE.Id = A.Id;
						
LBEXIT:
						IF @TranCount = 0
							COMMIT;
						END TRY
					BEGIN CATCH
						DECLARE @Error INT, @Message VARCHAR(4000), @XState INT;
						SELECT	@Error = ERROR_NUMBER() ,@Message = ERROR_MESSAGE() ,@XState = XACT_STATE();

						IF @XState = -1
							ROLLBACK;
						IF @XState = 1 AND @TranCount = 0
							rollback
						IF @XState = 1 AND @TranCount > 0
							ROLLBACK TRANSACTION  UpdateAttributeOrder;

						RAISERROR (''UpdateAttributeOrder: %d: %s'', 16, 1, @error, @message) ;
					END CATCH
				END
		');


		COMMIT TRANSACTION [Tran1];
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION [Tran1];
		DECLARE @ErrorNumber nchar(5), @ErrorMessage nvarchar(2048);
		SELECT
			@ErrorNumber = RIGHT('00000' + ERROR_NUMBER(), 5),
			@ErrorMessage = @ErrorNumber + ' ' + ERROR_MESSAGE();
		RAISERROR (@ErrorMessage, 16, 1);
	END CATCH