I were trying to achive paging through using a CTE etc, but ran into the following weither thing happening. The CTE allows me to use avariable as the ORder By field, although the CTE do not care at all what is in there? Have any one seen this or maybe can explain this?
USE AdventureWorks;
GO
DECLARE @.SortExpression Varchar(50)
Set @.SortExpression = 'SalesPersonID ASC';
WITH Sales_CTE (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT
ROW_NUMBER() OVER(Order by @.SortExpression) RowNumber,
SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
Select * From Sales_CTE;
WITH Sales_CTE1 (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT
ROW_NUMBER() OVER(Order by SalesPersonID ASC) RowNumber,
SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
Select * From Sales_CTE1
I know your post is a few months; however, I figured I'd post a response incase you or anyone else is interested in a workaround.
I came across the same problem with CTE (common table expressions) and sorting. this code below may work. I don't have adventureWorks installed so my code may cause errors if used exactly. Debugging shouldn't be a problem:
Code Snippet
USE AdventureWorks;
GO
DECLARE @.SortExpression Varchar(50)
Set @.SortExpression = 'SalesPersonID ASC';
DECLARE @.sql1 varchar(4000)
SET @.sql1 = 'SELECT RowNumber, SalesPersonID, NumberOfOrders, MaxDate
FROM
(
SELECT
ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,
SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
) tbl1'
exec sp_executesql @.sql1
If you're using a DataSource in .NET and want to implement paging, sorting, AND filtering (for searching or limiting data) then please read on. sp_executesql also supports bind variables. SQL will will reuse the cached execution plan, this SQL won't recalculate the execution plan each time the page, sort, or filter condition(s) change.
This procedure below will do the same as query above, let you page the data (if you have many salesPersonIDs), and will will let you limit which SalesPersonID you're looking at... all with bind variables for faster query execution using sp_executesql.
Code Snippet
CREATE PROCEDURE [dbo].[usp_getSalesOrders]
@.SalesPerson int,
@.startRowIndex int,
@.maximumRows int,
@.SortExpression varchar(20)
AS
BEGIN
SET NOCOUNT ON;
--if a sortExpression isn't passed in, give it a default
IF len(isnull(@.SortExpression, '') = 0
set @.SortExpression = 'SalesPersonID ASC'
DECLARE @.sql nvarchar(4000)
SET @.sql = 'SELECT SalesPersonID, NumberOfOrders, MaxDate
FROM
(
SELECT
ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,
SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader with(nolock)
WHERE
(SalesPersonID = @.SalesPerson OR @.SalesPerson IS NULL)
GROUP BY SalesPersonID
) t
WHERE t.RowNumber BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1'
exec sp_executesql @.sql,
N'@.SalesPerson int,@.startRowIndex int,@.maximumRows int',
@.NumOfOrders,
@.startRowIndex,
@.maximumRows;
END
You can further optimize this, and I welcome comments as it'll help to improve my own code.
Hope this helps!
Nick
No comments:
Post a Comment