Need urgent help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need urgent help

Author

Topic

shishirbabel
Starting Member

3 Posts

Posted - 2010-07-31 : 00:23:16

We have an e-commerce website with hundreds of products . Need new functionality added to the website where after every 24 hours products will be automatically rotated in random manner.

How it will work

After every 24 hours products will be rotated in following manner. For eg we have 300 products in the database then first time 1st 20 products will be rotated with products from 40 to 60 in numbers , next time 20 to 40 products will be rotated with 60 to 80 numbers and so on. Please make sure after every 24 hours previous state of rotation will be maintained so if 1 to 20 are are rotated with 40 to 60 then we will have to make sure we track location of last product rotated so that after every rotation next series will come till the end of products and once all products are finished in the database it will start from 1 .

You can add more columns to the database as per your requirement.

Attached are tables and sp's attached for your review.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Nitin>
-- Updated by: Shyam
-- Create date: <28 jan 2009>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[insert_ProductNew]
@Key nvarchar(50),
@ProductName nvarchar(50),
@ProductMainImagePath nvarchar(500),
@ProductSmallImagePath nvarchar(500),
@ProductVMImagePath nvarchar(500),
@ProductBigImagePath1 nvarchar(500),
@ProductBigImagePath2 nvarchar(500),
@ProductBigImagePath3 nvarchar(500),
@ProductSmallImagePath1 nvarchar(500),
@ProductSmallImagePath2 nvarchar(500),
@ProductSmallImagePath3 nvarchar(500),
@LongDescription nvarchar(max),
@Price numeric(18,2),
@PriceRangeID int,
@Quantity int,
@IsSingleVision bit,
@IsBifocalVision bit,
@BifocalVisionPrice numeric(18,2),
@IsVarifocalVision bit,
@VarifocalVisionPrice numeric(18,2),
@IsVisible bit,
--@Rank int,
@IsHotDeal bit,
@HotDealPrice numeric(18,2),
@LensTypeID int,
@FrameSizeID int,
@FrameMaterialID int,
@FrameLookID int,
@FrameTypeID int,
@IsLatestStyle bit,
@CategoriesXML text,
@ColorXML text,
@TintedLensColorXML text,
@PolarisedLensColor nvarchar(50),
@TransitionLensColor nvarchar(50),
@IsTintedLens bit,
@IsTransitionLens bit,
@IsPolarisedLens bit,
@TintedPrice numeric(18,2),
@TransitionPrice numeric(18,2),
@PolarisedPrice numeric(18,2),
@IsAntiReflective bit,
@AntiReflectivePrice numeric(18,2),
@IsUV100protective bit,
@UV100protectivePrice numeric(18,2),
@IsScratchResistant bit,
@ScratchResistantPrice numeric(18,2),
@PageURL nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
BEGIN TRY
begin transaction
declare @a int

SET NOCOUNT ON;

set @a=1
--insert into product

-- Insert statements for procedure here
INSERT INTO [Product] (
ProductName,
ProductMainImagePath,
ProductSmallImagePath,
ProductVMImagePath,
ProductBigImagePath1,
ProductBigImagePath2,
ProductBigImagePath3,
ProductSmallImagePath1,
ProductSmallImagePath2,
ProductSmallImagePath3,
LongDescription,
Price,
PriceRangeID,
Quantity,
IsSingleVision,
IsBifocalVision,
IsVarifocalVision,
IsVisible,
--@Rank int,
IsHotDeal,
LensTypeID,
FrameSizeID,
FrameMaterialID,
FrameLookID,
FrameTypeID,
IsLatestStyle,
--@CategoriesXML text,
--@ColorXML text,
--@TintedLensColorXML text,
PolarisedColor,
TransitionColor,
IsTintedLens,
IsTransitionLens,
IsPolarisedLens,
IsAntiReflectiveCoating,
IsUV100protective,
IsScratchResistant,
BifocalVisionPrice,
VarifocalVisionPrice,
HotDealPrice,
TintedPrice,
TransitionPrice,
PolarisedLensPrice,
AntiReflectiveCoatingPrice,
UV100protectivePrice,
ScratchResistantPrice,
PageURL
)
--VALUES (
select
EncryptByPassPhrase(@Key, convert(varchar(50), @ProductName)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductMainImagePath)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductSmallImagePath)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductVMImagePath)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductBigImagePath1)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductBigImagePath2)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductBigImagePath3)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductSmallImagePath1)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductSmallImagePath2)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductSmallImagePath3)),
EncryptByPassPhrase(@Key, convert(varchar(max), @LongDescription)),
@Price,
@PriceRangeID,
@Quantity,
@IsSingleVision,
@IsBifocalVision,
@IsVarifocalVision,
@IsVisible,
--@Rank int,
@IsHotDeal,
@LensTypeID,
@FrameSizeID,
@FrameMaterialID,
@FrameLookID,
@FrameTypeID,
@IsLatestStyle,
--@CategoriesXML text,
--@ColorXML text,
--@TintedLensColorXML text,
@PolarisedLensColor,
@TransitionLensColor,
@IsTintedLens,
@IsTransitionLens,
@IsPolarisedLens,
@IsAntiReflective,
@IsUV100protective,
@IsScratchResistant,
case when(@IsBifocalVision=1) then @BifocalVisionPrice else null end,
case when(@IsVarifocalVision=1) then @VarifocalVisionPrice else null end,
case when(@IsHotDeal=1) then @HotDealPrice else null end,
case when(@IsTintedLens=1) then @TintedPrice else null end,
case when(@IsTransitionLens=1) then @TransitionPrice else null end,
case when(@IsPolarisedLens=1) then @PolarisedPrice else null end,
case when(@IsAntiReflective=1) then @AntiReflectivePrice else null end,
case when(@IsUV100protective=1) then @UV100protectivePrice else null end,
case when(@IsScratchResistant=1) then @ScratchResistantPrice else null end,
EncryptByPassPhrase(@Key, convert(varchar(max), @PageURL))

set @a=2
-- if(@@error>0)
-- goto RollTran
declare @ProductID int

--update Rank
set @ProductID=scope_identity()
update [Product] set Rank =@ProductID, RankHotDeal=(select case when(max(RankHotDeal) is null) then 1 else Max(RankHotDeal)+1 end from Product), RankLatest =(select case when(max(RankLatest) is null) then 1 else Max(RankLatest)+1 end from Product) where ID=@ProductID

--insert into product_category
declare @XMLCategoriesXML int
exec sp_xml_preparedocument @XMLCategoriesXML output, @CategoriesXML
insert into ProductCategories(ProductID, CategoryID,Rank) select @ProductID, CategoryID,(select case when(max(Rank) is null) then 1 else Max(Rank)+1 end from ProductCategories) from openxml(@XMLCategoriesXML,'NewDataSet/dt_Category', 2) with (CategoryID int)

--insert into product_color
set @a=3
declare @XMLColorXML int
exec sp_xml_preparedocument @XMLColorXML output, @ColorXML
insert into Product_Color(ProductID,ColorID) select @ProductID, ColorID from openxml(@XMLColorXML,'NewDataSet/dt_Color',2) with (ColorID int)

--insert into product_tintedcolor
set @a=4
declare @XMLTintedLensColorXML int
exec sp_xml_preparedocument @XMLTintedLensColorXML output, @TintedLensColorXML
insert into Product_TintedColor(ProductID,TintedColorID) select @ProductID, TintedColorID from openxml(@XMLTintedLensColorXML,'NewDataSet/dt_TintedColor',2) with (TintedColorID int)
set @a=5
END TRY
BEGIN CATCH
Rollback transaction
--select @a as a
select '0'
return
END CATCH;
commit transaction
select @ProductID
return

END

------

CREATE TABLE [dbo].[Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](50) NOT NULL,
[ProductMainImagePath] [nvarchar](500) NOT NULL,
[ProductSmallImagePath] [nvarchar](500) NOT NULL,
[ProductVMImagePath] [nvarchar](500) NOT NULL,
[ProductBigImagePath1] [nvarchar](500) NOT NULL,
[ProductBigImagePath2] [nvarchar](500) NOT NULL,
[ProductBigImagePath3] [nvarchar](500) NOT NULL,
[ProductSmallImagePath1] [nvarchar](500) NOT NULL,
[ProductSmallImagePath2] [nvarchar](500) NOT NULL,
[ProductSmallImagePath3] [nvarchar](500) NOT NULL,
[LongDescription] [nvarchar](max) NULL,
[Price] [numeric](18, 2) NOT NULL,
[PriceRangeID] [int] NULL,
[Quantity] [int] NULL,
[IsSingleVision] [bit] NOT NULL,
[IsBifocalVision] [bit] NOT NULL,
[BifocalVisionPrice] [numeric](18, 2) NULL,
[IsVarifocalVision] [bit] NOT NULL,
[VarifocalVisionPrice] [numeric](18, 2) NULL,
[IsVisible] [bit] NOT NULL,
[Rank] [int] NULL,
[IsDelete] [bit] NOT NULL,
[IsHotDeal] [bit] NOT NULL,
[HotDealPrice] [numeric](18, 2) NULL,
[LensTypeID] [int] NULL,
[FrameSizeID] [int] NULL,
[FrameMaterialID] [int] NULL,
[FrameLookID] [int] NULL,
[FrameTypeID] [int] NULL,
[IsLatestStyle] [bit] NOT NULL,
[IsTintedLens] [bit] NOT NULL,
[TintedPrice] [numeric](18, 2) NULL,
[IsTransitionLens] [bit] NOT NULL,
[TransitionPrice] [numeric](18, 2) NULL,
[TransitionColor] [nvarchar](50) NULL,
[IsPolarisedLens] [bit] NOT NULL,
[PolarisedLensPrice] [numeric](18, 2) NULL,
[PolarisedColor] [nvarchar](50) NULL,
[IsAntiReflectiveCoating] [bit] NOT NULL,
[AntiReflectiveCoatingPrice] [numeric](18, 2) NULL,
[IsUV100Protective] [bit] NOT NULL,
[UV100ProtectivePrice] [numeric](18, 2) NULL,
[IsScratchResistant] [bit] NOT NULL,
[ScratchResistantPrice] [numeric](18, 2) NULL,
[Rating] [int] NOT NULL,
[PageURL] [nvarchar](50) NULL,
[RankHotDeal] [int] NULL,
[RankLatest] [int] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Define the display Order' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Product', @level2type=N'COLUMN',@level2name=N'Rank'
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_ProductVMImagePath] DEFAULT (N'') FOR [ProductVMImagePath]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsDelete] DEFAULT ('False') FOR [IsDelete]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsHotDeal] DEFAULT ('False') FOR [IsHotDeal]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_HotDealPrice] DEFAULT ((0)) FOR [HotDealPrice]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsLatestStyle] DEFAULT ('False') FOR [IsLatestStyle]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsTintedLens] DEFAULT ('False') FOR [IsTintedLens]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_TintedPrice] DEFAULT ((0)) FOR [TintedPrice]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsTransitionLens] DEFAULT ('False') FOR [IsTransitionLens]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_TransitionPrice] DEFAULT ((0)) FOR [TransitionPrice]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_TransitionColor] DEFAULT (N'') FOR [TransitionColor]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsPolarisedLens] DEFAULT ('False') FOR [IsPolarisedLens]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_PolarisedLens] DEFAULT ((0)) FOR [PolarisedLensPrice]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_PolarisedColor] DEFAULT (N'') FOR [PolarisedColor]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsAntiReflectiveCoating] DEFAULT ('False') FOR [IsAntiReflectiveCoating]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_AntiReflectiveCoatingPrice] DEFAULT ((0)) FOR [AntiReflectiveCoatingPrice]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsUV100Protective] DEFAULT ('False') FOR [IsUV100Protective]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_UV100ProtectivePrice] DEFAULT ((0)) FOR [UV100ProtectivePrice]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsScratchResistant] DEFAULT ('False') FOR [IsScratchResistant]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_ScratchResistant] DEFAULT ((0)) FOR [ScratchResistantPrice]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_Rating] DEFAULT ((2)) FOR [Rating]
GO

Kristen
Test

22859 Posts

Posted - 2010-07-31 : 02:04:12

Can't you just use the ID number? And remember the highest number you got to for next time?


INSERT INTO @TempTable
SELECT TOP 20 ID
FROM Product
WHERE ID > @MaxFromLastTime
ORDER BY ID
SELECT @intRowCount = @@ROWCOUNT

IF @intRowCount < 20 -- Presumably got to the end
INSERT INTO @TempTable
SELECT TOP (20 - @intRowCount) ID
FROM Product
ORDER BY ID

UPDATE MyConfigTable
SET [MaxFromLastTime] = (SELECT MAX(ID) FROM @TempTable)

-- Output
SELECT Col1, Col2, ...
FROM Product AS P
    JOIN @TempTable AS T
        ON T.ID = P.ID

I'm curious why you have chosen to EncryptByPassPhrase the Product Name and Image Path?

We have had problems with handling XML (back in SQL 2000) where we used TEXT rather than Ntext - I can't remember the problem, but I think it was pretty trivial - like we had a "\" or somesuch in the data, although it might have been an Extended Character. May have been fixed in later versions (sp_xml_preparedocument is documented as taking TEXT of Ntext)


ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsScratchResistant] DEFAULT ('False') FOR [IsScratchResistant]

Not sure that's going to work for a bit field is it? (there are several other constraints defined similarly)


ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_ScratchResistant] DEFAULT ((0)) FOR [ScratchResistantPrice]

FWIW we NEVER use zero as a default price - in case it gets sold at that. We prefer NULL but where that isn;t possible we use 99,999,999 to avoid anyone accidentally buying it and so that it shows up like a sore-thumb!

Celko
Starting Member

23 Posts

Posted - 2010-07-31 : 20:47:20

Here is the formula for a 31-bit additive congruency generator. These things are like a random number generator, but every number appears one and only one time in the cycle.

UPDATE generator
SET keyval = keyval/2 + MOD(MOD(keyval, 2) + MOD(keyval/8, 2), 2) * 2^30;

Here is the same algorithm implemented in C.

int asequence()
{static int n = 1;
n = n>>1 | (( n^n>>3 ) & 1) << 30;
return n;}

Set up a column for this value, insert the first 300 or whatever values, sort on the congruency and return your blocks of 20 rows based on it. Then generate another set of 300 values. This will guarantee randomness with duplication.

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources