CTE behaviour in SQL 2005

SQL Server Database Engine

Gertus


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




Top

Re: CTE behaviour in SQL 2005

Nick V.


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






Top