|
|
Posted by Erland Sommarskog on 12/03/05 13:26
(rhungund@gmail.com) writes:
> 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.
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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|