Stored procedura
Imam proceduru za pretragu kataloga, pošto je u pitanju baza na kojoj ce se kaciti takodje web aplikacija odradio sam query koji ce mi vracati odredjeni broj redova (u zavisnosti da li ce proceduru pozvati web ili desktop aplikacija). Katalag ce imati maximalno 100000 do 150000 redova. Kada u delu "ROW_NUMBER() OVER (order by @sortBy) as RowNumber " prosledim parametar @sortBy rezultat nije sortiran po prosledjenom parametru. Naravno sve to fino radi kada se umesto @sortBy napiše ime kolone. Ima li neko ideju šta to nije u redu?
CREATE PROC [dbo].[GetSearItem]
@sortBy nvarchar(20),
@PageSize int,
@PageNumber int,
@itemNu nvarchar(10),
@Desc nvarchar(50),
@ParN nvarchar(50),
@Manuf nvarchar(50),
@Mod nvarchar(50)
AS
Declare @RowStart int
Declare @RowEnd int
if @PageNumber > 0
Begin
SET @PageNumber = @PageNumber -1
SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;
With Cust AS
( SELECT *,(SELECT COUNT(ItemNu)
FROM [IPCS2SQL].[dbo].[tbl_Items]
WHERE ((ItemNu like '%' + @itemNu + '%') And (ItemDesc like '%' + @Desc + '%') AND
(PartNum like '%' + @ParN + '%') AND (Manufacturer like '%' + @Manuf + '%') AND
([Model] like '%' + @Mod + '%')) ) AS TotR,
ROW_NUMBER() OVER (order by @sortBy) as RowNumber
FROM [IPCS2SQL].[dbo].[tbl_Items]
WHERE ((ItemNu like '%' + @itemNu + '%') And (ItemDesc like '%' + @Desc + '%') AND
(PartNum like '%' + @ParN + '%') AND (Manufacturer like '%' + @Manuf + '%') AND
([Model] like '%' + @Mod + '%')) )
select *
from Cust
Where RowNumber >= @RowStart and RowNumber <= @RowEnd
END
 |  |
 |
