Establishing Precedence In ORDERBY Condition Causing Problems.
Jun 11, 2007
Hi.
I really need some advice on fine-tuning a stored procedure
that is the meat of the search logic on my site. Customers
are allowed to save searches, which dumps the search logic
in a table called SavedSearches for later access to the search.
My problem started with the ORDERBY condition used for zipcode
searches. The condition did something like:
"order by CASE WHEN userID=67 THEN 1 WHEN userID=103 THEN 2 WHEN
userID=102 THEN 3 WHEN userID=81 THEN 4"
Of course, this fails when a customer described in the saved search
results deletes his profile.
I have since attempted to brace against this problem by adding a
UserPrecendence table with the following columns: email_address,
up_order (or, user precedence order), and userID.
Since I have made the precedence changes, I have been unsuccessful in
getting any results (data) back from the query. I think it has to do
with the change but am not quite sure what I am doing wrong.
I would appreciate it is somebody could take a look at my sproc with
particular attention to how precedence is handled in the ORDERBY
condition. Maybe you can see something I can not?
As always, much appreciated.
PS - In addition to the UserPrecedence change, I have attempted to add
paging - returning N amount of pages per request based on passed-in
paramaters. I'd appreciate it if you could take a quick glance here
also just to make sure my logic is OK.
----------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[sp_PeopleSearch]
@pagenum INT = 1,
@perpage INT = 10
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ubound INT,
@lbound INT,
@pages INT,
@rows INT
SELECT
@rows = COUNT(*),
@pages = COUNT(*) / @perpage
FROM
(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)
IF @rows % @perpage != 0 SET @pages = @pages + 1
IF @pagenum @pages SET @pagenum = @pages
IF @pagenum < 1 SET @pagenum = 1
SET @ubound = @perpage * @pagenum
SET @lbound = @ubound - (@perpage - 1)
SELECT
CurrentPage = @pagenum,
PageSize = @perpage,
TotalPages = @pages,
TotalRows = @rows,
UpperBoundary = @ubound,
LowerBoundary = @lbound
-- this method determines the string values
-- for the first desired row, then sets the
-- rowcount to get it, plus the next n rows
DECLARE
@gender VARCHAR(50),
@country VARCHAR(50),
@orderby INTEGER,
@low VARCHAR(50),
@high VARCHAR(50),
@photo VARCHAR(50),
@sort INTEGER
SET ROWCOUNT @lbound
SELECT
@gender = saved_sex,
@country = saved_country,
@orderby = saved_orderby,
@low = saved_fage,
@high = saved_tage,
@sort = saved_sort,
@photo = saved_photo_string
FROM
(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)
ORDER BY CASE @sort
WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order
END DESC
SET ROWCOUNT @perPage
SELECT COALESCE
(
tab1.emailAddress,
tab2.user_name,
tab3.email_address,
tab4.email_address,
tab5.email_address,
tab6.email_address
)
id ,
tab1.bday_day ,
tab1.bday_month ,
tab1.bday_year ,
tab1.gender ,
tab1.zipCode ,
tab1.siteId ,
tab1.userID ,
tab2.photo_location ,
tab2.photo_name ,
tab2.photo_default ,
tab2.no_photo ,
tab3.headline ,
tab3.about_me ,
tab4.login_date ,
tab4.login_isonline,
tab5.up_order,
tab6.saved_orderby,
tab6.saved_sort,
tab6.saved_fage,
tab6.saved_tage
FROM
(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)
WHERE
tab1.gender = @gender
AND tab1.country = @country
AND tab1.bday_year BETWEEN @low AND @high
AND tab2.photo_default = 1 + @photo
--and not tab2.no_photo = 1
--firstName + '~' + lastName
-->= @fname + '~' + @lname
ORDER BY CASE @sort
WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order
END DESC
SET ROWCOUNT 0
END
View 7 Replies
Dec 19, 2007
Hi,
Can some one help me to complete this query.
My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time.
Existing procedure:
ALTER PROCEDURE [dbo].[spGetAllItems]
@condition varchar(7500),
@ModelId int =0,
@CategoryId int=0,
@SourcingType int=0,
@StartRow as int = 1,
@StopRow as int = 100000
AS
declare @erow int
declare @Cond varchar(255)
select @Cond=''
if @CategoryId<>0
select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar)
if @SourcingType<>0
select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar)
if @StopRow=-1
select @StopRow=60
set @erow=@StartRow + @StopRow
exec('Declare @t_table table
(
[SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL,
[id] [int],
[category] [int] NULL ,
[model] [int] NULL ,
[mtype] [varchar] (50) NULL,
[itemclass] [varchar] (50) NULL,
[itemcode] [nvarchar] (25) NULL ,
[ItemCondition] [tinyint] NULL ,
[SourcingType] int,
[Title] [nvarchar] (50) NULL ,
[Description] [nvarchar] (4000) NULL ,
[InternalUse] [nvarchar] (4000) NULL,
[CompatibleModels] [varchar] (8000) NULL ,
[Quantity] [int] NULL ,
[MinOrder] [int] NULL ,
[Warranty] [tinyint] NULL ,
[Price1] [decimal](6, 2) NULL ,
[Price2] [decimal](6, 2) NULL ,
[Price3] [decimal](6, 2) NULL ,
[Price4] [decimal](6, 2) NULL ,
[LotSale] [bit],
[PricingRule] int ,
[Weight] [decimal](7, 2) NULL ,
[PackageSize] [int] NULL ,
[promo] [int] NULL ,
[SmallImage] [nvarchar] (255) NULL ,
[BigImage] [nvarchar] (255) NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Info1] [nvarchar] (50) NULL ,
[Info2] [nvarchar] (50) NULL ,
[Info3] [nvarchar] (50) NULL ,
[Info4] [nvarchar] (50) NULL ,
[Info5] [nvarchar] (50) NULL ,
[Info6] [nvarchar] (50) NULL ,
[Info7] [nvarchar] (50) NULL ,
[Info8] [nvarchar] (50) NULL ,
[Info9] [nvarchar] (50) NULL ,
[Info10] [nvarchar] (50) NULL ,
[Info11] [nvarchar] (50) NULL ,
[Info12] [nvarchar] (50) NULL ,
[Info13] [nvarchar] (50) NULL ,
[Info14] [nvarchar] (50) NULL ,
[Info15] [nvarchar] (50) NULL ,
[Info16] [nvarchar] (50) NULL ,
[Info17] [nvarchar] (50) NULL ,
[Info18] [nvarchar] (50) NULL ,
[Info19] [nvarchar] (50) NULL ,
[Info20] [nvarchar] (50) NULL ,
[Info21] [nvarchar] (50) NULL ,
[Info22] [nvarchar] (50) NULL ,
[Info23] [nvarchar] (50) NULL ,
[Info24] [nvarchar] (50) NULL ,
[Info25] [nvarchar] (50) NULL ,
[Status] [tinyint] NULL ,
[AllowBuy] [char] (1) NULL ,
[PageName] [varchar] (200) NULL ,
[Locality] [int] NULL ,
[Location] [int] NULL ,
[CreatedBy] [int] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [int] NULL ,
[UpdatedOn] [datetime] NULL,
[BrandId] int,
[CategoryId] int,
[ModelId] int,
[ModelName] varchar(255),
[BrandName] varchar(255),
[CategoryName] varchar(255)
);
DECLARE @EndRow int
DECLARE @reccount int
select @reccount=count(*) from (SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + '
Union
SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + ') tbl
set @EndRow= + ' + @StartRow + '+' + @StopRow + '
--Set RowCount @EndRow;
insert @t_table
(
[id] ,
[category] ,
[model] ,
[mtype],
[itemclass],
[itemcode] ,
[ItemCondition] ,
[SourcingType],
[Title] ,
[Description] ,
[InternalUse],
[CompatibleModels],
[Quantity] ,
[MinOrder] ,
[Warranty] ,
[Price1] ,
[Price2] ,
[Price3] ,
[Price4] ,
[LotSale] ,
[PricingRule],
[Weight] ,
[PackageSize] ,
[promo] ,
[SmallImage] ,
[BigImage] ,
[StartDate] ,
[EndDate] ,
[Info1] ,
[Info2] ,
[Info3] ,
[Info4] ,
[Info5] ,
[Info6] ,
[Info7] ,
[Info8] ,
[Info9] ,
[Info10] ,
[Info11] ,
[Info12] ,
[Info13] ,
[Info14] ,
[Info15] ,
[Info16] ,
[Info17] ,
[Info18] ,
[Info19] ,
[Info20] ,
[Info21] ,
[Info22] ,
[Info23] ,
[Info24] ,
[Info25] ,
[Status] ,
[AllowBuy] ,
[PageName] ,
[Locality] ,
[Location] ,
[CreatedBy] ,
[CreatedOn] ,
[UpdatedBy] ,
[UpdatedOn] ,
[BrandId] ,
[CategoryId] ,
[ModelId],
[ModelName],
[BrandName],
[CategoryName]
)
select * from (SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,
tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl
Union
SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,
tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc;
SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow
ORDER BY UpdatedOn DESC
')
RETURN
can anybody help me pls. ITs very urgent.
Thanks in advance.
View 1 Replies
View Related