SP and ADO Page Property Not Working Properly

Hi all...I have a stored proc that works fine except I can't get the
paging property to work in ASP. It seems that because I'm building the
SQL withing the SP is causing the problem. If I don't build the SQL as
a string paging works. In my ASP page, the pagecount property returns
-1 for some reason.

Here's the proc:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure LS_DisplayAllCategoriesBoolean
@aSections varchar(255),
@field varchar(255)

As

declare @sql varchar(1000)
SET NOCOUNT ON

set @sql = 'SELECT DISTINCT ls_product_catalog.id,
ls_product_catalog.ItemNumber, ls_product_catalog.Name,
ls_product_catalog.[Descriptor], ls_product_catalog.Price,
ls_product_catalog.Shade_Name, ls_product_catalog.Sale_Price,
ls_product_catalog.Sale_Desc
FROM dbo.ls_brand INNER JOIN
dbo.ls_product_catalog ON dbo.ls_brand.nID =
dbo.ls_product_catalog.CategoryID INNER JOIN
dbo.ls_product_category ON dbo.ls_product_catalog.id =
dbo.ls_product_category.nProductID
WHERE dbo.ls_brand.bVisible = 1 and
dbo.ls_product_category.nCategoryID in (Select IntValue
from dbo.CsvToInt(''' + @aSections + ''') ) '

if @field = 'bNew'
begin
set @sql = @sql + ' AND bNew = 1 AND
(dbo.ls_product_catalog.bGiftOnly is null or
dbo.ls_product_catalog.bGiftOnly = 0)'
set @sql = @sql + ' order by
ls_product_catalog.[Descriptor]'
end
if @field = 'bGift'
begin
set @sql = @sql + ' AND bGift = 1 or bGiftOnly =1'
set @sql = @sql + ' order by
ls_product_catalog.[Descriptor]'
end

if @field = 'bGiftOnly'
begin
set @sql = @sql + ' AND bGiftOnly= 1'
set @sql = @sql + ' order by
ls_product_catalog.[Descriptor]'
end

if @field = 'sale_price'
begin
set @sql = @sql + ' AND (sale_price is not null OR
sale_desc is not
null)'
set @sql = @sql + ' order by ls_product_catalog.Name'
end

exec(@sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON

re: SP and ADO Page Property Not Working Properly

(rhungund@gmail.com) writes:[color=blue]
> Hi all...I have a stored proc that works fine except I can't get the
> paging property to work in ASP. It seems that because I'm building the
> SQL withing the SP is causing the problem. If I don't build the SQL as
> a string paging works. In my ASP page, the pagecount property returns
> -1 for some reason.[/color]

I don't think the paging stuff in ADO is very effective for paging. It
works (sometimes), but the overhead may be devastative.

Check out Aaron Bertrand's www.aspfaq.com and search for "paging". He
has a few articles about paging where you can find alternatives to what
ADO offers.

In the meanwhile, I can offer suggestions to relieve you from the use
of dynamic SQL. It appears that you use dynamic SQL for two reasons:
1) To handle the comma-separated list 2) to select a field for the search,
and also determine order.

The comma-separated list is simple: unpack it with a table-valued function
and join to that function. I describe several methods in an article on
my web site. To get a head start, go directly to
http://www.sommarskog.se/arrays-in-sql.html#iterative.

The @field parameter requires a little more work, but here is a
possibility:

AND CASE @field
WHEN 'bNew' THEN CASE WHEN bNew = 1 AND
coalesce(pc.bGiftOnly, 0) = 0
THEN 1
ELSE 0
END
WHEN 'bGift' THEN bGift | bGiftOnly
WHEN 'sale_price' THEN CASE WHEN sale_price IS NOT NULL OR
sale_desc IS NOT NULL
THEN 1
ELSE 0
END
ELSE 1
END = 1
ORDER BY CASE @field
WHEN 'sale_price' THEN ls_product_catalog.Name
ELSE ls_product_catalog.[Descriptor]
END

I'm here using the CASE *expression* to handle the dynamic stuff with
@field. Note the emphasis on expression, this is different from many
other languages. An expression must return a value, and you cannot return
a value like "a = 0", as SQL does not have a boolean data type. But for
every check, you can fork of yet another CASE expression. For bGift, as
you can see, I was able to skip the CASE, by using the fairly obscure
bitwise OR operator.

A CASE expression always returns the one and same data type. If different
branches returns different data types, there will be conversion to the
type that is highest in SQL Servers data-type precedence order.

For details on syntax, please see Books Online.

While the above is neat, I should add that it is not always a feasible
solution. Your solution with dynamic SQL has the advantage of including
only what is at hand for the current search. If the alternatives includes
indexed columns, performance is likely to suffer if you include all possible
alternatives. But it does not seems to me that this should be the case
here.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx