Query with Row Number,Union and dynamic Orderby.

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

Query with Row Number,Union and dynamic Orderby.

Author

Topic

sivrin
Starting Member

4 Posts

Posted - 2007-12-19 : 21:40:14

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.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-19 : 22:44:16

Dup post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94514

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources