December 29, 2010 at 10:01 am
Hi! i have two tables that are category and products. when i select a catageroy(on my asp.net project) i will list products associated with that category. and i do paging on this list. how can i write a stored procedure that do this process?
i tried to create a temporary table using with #tablename as(...) i list products, but then i couldn't make paging on this temptable.
please help.Thanks...
December 29, 2010 at 10:09 am
here's an example using row_number of using that to page information;
if you gave us more details, specifically the actual table schemas, and maybe a sample select statement, we could offer more concrete advice;
this example is paging in batches of 25; i'm getting the third group in this example.
declare @WhichPage int
Set @WhichPage = 3
SELECT * FROM (
select row_number() OVER(ORDER BY ID) AS RWNBR, * FROM SYSOBJECTS) MYALIAS
WHERE RWNBR BETWEEN (25 * (@WhichPage -1)) AND (25 * (@WhichPage -1)) + 25
---returns rows 75 thru 100 inclusive (26 rows)
Lowell
December 29, 2010 at 10:47 am
Hi again. My problem is this completely. i develop a we project(asp.net). there are categories,subcategories. For example i select laptop category. PAge will show products that has CategoryID of Laptop. Let's sat 80 produxts exists on laptop category.Page will show 10 products in the page. Then iwill click page2 ,then other 10 products will be shown.
i do this for all products. but i couldn't for category products. first i select products associated with that category,then i do paging on that products.
As i said, i have 2 tables. Category,Poducts. there is a field CateoryID on products table. i use this field for categoryid of category.
Thnks again.
December 29, 2010 at 11:08 am
i was looking for real code specifics..CREATE TABLE Category....
and then how you wanted to filter the products:
SELECT * FROM PRODUCTS WHERE CATEGORYID = ....
those details are what i'd need to make anything except a guess.
Lowell
December 29, 2010 at 11:24 am
i tried like this.
ALTER PROCEDURE [dbo].[sp_listcategoryproducts]
@dolar float,
@katid int,
@sayfano int
as
BEGIN
with myurun
as
(
select (ROW_NUMBER()over(order by UrunKategoriID))as sirano,UrunKodu,UrunAdi,UrunResim,UrunStok,UrunFiyat,
case
when UrunKDVDurumID=1 then 'KDV Dahil'
else '+KDV'
end as KDVDurum,
dbo.f_urunfiyati(UrunKDVDurumID,UrunParaTipi,UrunFiyat,@dolar) as ToplamTutar,
case
when UKampanya=1 then 'Indirimli'
end as indirim,
case
when UKampanya=1 then dbo.f_indirimlifiyat(UrunKDVDurumID,UIndirim,UrunParaTipi,UrunFiyat,@dolar)
when UKampanya=0 then '0'
end as indirimlifiyat,
case
when UrunParaTipi=1 then 'TL'
else '$'
end as ParaTipi
from Urunler where UrunKategoriID=@katid
)
select * from myurun
)
i tried to list products that belongs to specified UrunKategoriID.i created a temp table like this. problem is after this. Now i need to do paging. i also tried to create a temp table using create table #tempproduct. but i can't load above query on it.
December 29, 2010 at 12:28 pm
well, adding two optional parameters to your proc, i'd suggest something like this:
ALTER PROCEDURE [dbo].[sp_listcategoryproducts]
@dolar float,
@katid int,
@sayfano int,
--used to set group size of paging
@BatchSize int = 10,
--used to decide which batch to get
@WhichPage int = 1
as
BEGIN
with myurun
as
(
select
(ROW_NUMBER()over(order by UrunKategoriID))as sirano,
UrunKodu,
UrunAdi,
UrunResim,
UrunStok,
UrunFiyat,
case
when UrunKDVDurumID=1 then 'KDV Dahil'
else '+KDV'
end as KDVDurum,
dbo.f_urunfiyati(UrunKDVDurumID,UrunParaTipi,UrunFiyat,@dolar) as ToplamTutar,
case
when UKampanya=1 then 'Indirimli'
end as indirim,
case
when UKampanya=1 then dbo.f_indirimlifiyat(UrunKDVDurumID,UIndirim,UrunParaTipi,UrunFiyat,@dolar)
when UKampanya=0 then '0'
end as indirimlifiyat,
case
when UrunParaTipi=1 then 'TL'
else '$'
end as ParaTipi
from Urunler where UrunKategoriID=@katid
)
select * from myurun
WHERE sirano BETWEEN (@BatchSize * (@WhichPage -1)) AND (@BatchSize * (@WhichPage -1)) + @BatchSize
END --PROC
Lowell
December 29, 2010 at 12:54 pm
oh. thank you so much. this is what i want. Reaaly so thanks.
i tried
"select * from myurun
WHERE sirano BETWEEN (@BatchSize * (@WhichPage -1)) AND (@BatchSize * (@WhichPage -1)) + @BatchSize
"
part all using
declare @Bas=....
..
..
that is i declared a variable.so query was giving error.
Thanks again.
December 29, 2010 at 3:18 pm
CELKO (12/29/2010)
sa.ordekci (12/29/2010)
Hi! i have two tables that are category and products. when i select a catageroy(on my asp.net project) i will list products associated with that category. and i do paging on this list. how can i write a stored procedure that do this process?i tried to create a temporary table using with #tablename as(...) i list products, but then i couldn't make paging on this temptable.
please help.Thanks...
You are missing some basic concepts. Categories are attributes of entities, in this case I would assume that you have "product_cat" and it ought to be a column in the "Products" table.
Joe, help me to understand. Is he not supposed to have two tables? Or are you mentioning this because he didn't list the key in the Products table?
As usual, I R confused...
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 29, 2010 at 11:57 pm
Hi. i guess, i wrote something wrong.(also my english is not very well:)) )
i have 2 table: Category and Products. In products table, there is a field that suply relation with Category table. Its name is categoryid.
i wrote as paging. Because ,in most of resource article,writings, etc.. i read , this process(showing data like parts, that is not all in one time)is called as paging. so i wrote as paging.
anyway, i'll take into account things you says.
Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply