Skip to content

Instantly share code, notes, and snippets.

@imranbaloch

imranbaloch/Funct, SP SQL

Created March 12, 2015 12:36
Show Gist options
  • Save imranbaloch/3fa1ce354be4c07f541e to your computer and use it in GitHub Desktop.
Save imranbaloch/3fa1ce354be4c07f541e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment