March 12, 2009 at 1:56 pm
I have a stored procedure that I am using with my asp.net page that passes variables to allow sorting based upon the header that is selected in my table. I do not think my stored procedure is 100% correct as I am unable to sort throughout the entire dataset when the @sortexpression is passed.
Can someone take a look and help me with my select statements below? I think one of the last two select statements need to be changed somehow.
thanks!
Jenise
-----------------------------------------------------------------
SET @StartRowIndex = @StartRowIndex + 1
IF LEN(@sortExpression) = 0
BEGIN
SET @sortExpression = 'SAID'
END
BEGIN
WITH EmployeeCTE
AS
(
SELECT
UID,
1 AS Level,
CONVERT(VARBINARY(MAX), UID) AS thePath
FROM tblUsers
WHERE MID = @UID
UNION ALL
SELECT
E.UID,
x.Level + 1 AS Level,
x.thePath + CONVERT(VARBINARY(MAX), E.UID) AS thePath
FROM tblUsers E
JOIN EmployeeCTE x ON x.UID = E.MID
),
TempTable AS (
SELECT dbo.tblHeader.SAID, dbo.tblAccount.Type, dbo.tblRegions.Region, dbo.tblParentAccount.ParentAccountName, dbo.tblAccount.AccountName, dbo.tblHeader.EEID,
(SELECT u.UID
FROM dbo.tblUsers AS u INNER JOIN dbo.tblIndiv_Visibility AS v ON u.UID = v.UID
WHERE (v.SAID = dbo.tblHeader.SAID) AND (u.APTitle = 'AM')) AS AMID,
(SELECT u.FirstName + ' ' + u.LastName AS Expr1
FROM dbo.tblUsers AS u INNER JOIN dbo.tblIndiv_Visibility AS v ON u.UID = v.UID
WHERE (v.SAID = dbo.tblHeader.SAID) AND (u.APTitle = 'AM')) AS AM,
(SELECT u.UID
FROM dbo.tblUsers AS u INNER JOIN dbo.tblIndiv_Visibility AS v ON u.UID = v.UID
WHERE (v.SAID = dbo.tblHeader.SAID) AND (u.APTitle = 'FAE')) AS FAEID,
(SELECT u.FirstName + ' ' + u.LastName AS Expr1
FROM dbo.tblUsers AS u INNER JOIN dbo.tblIndiv_Visibility AS v ON u.UID = v.UID
WHERE (v.SAID = dbo.tblHeader.SAID) AND (u.APTitle = 'FAE')) AS FAE
FROM dbo.tblAccount INNER JOIN
dbo.tblHeader ON dbo.tblAccount.AID = dbo.tblHeader.AID INNER JOIN
dbo.tblIndiv_Visibility ON dbo.tblHeader.SAID = dbo.tblIndiv_Visibility.SAID INNER JOIN
dbo.tblParentAccount ON dbo.tblAccount.PAID = dbo.tblParentAccount.PAID INNER JOIN
dbo.tblRegions ON dbo.tblAccount.RID = dbo.tblRegions.RID INNER JOIN
(SELECT UID FROM EmployeeCTE) HIEARARCHY
ON dbo.tblIndiv_Visibility.UID = HIEARARCHY.UID
WHERE (dbo.tblHeader.Active = '1') AND (dbo.tblAccount.Active = '1')
), PagedList AS
(SELECT SAID, [Type], Region, ParentAccountName, AccountName, EEID, AMID, AM, FAEID, FAE, ROW_NUMBER() OVER (ORDER BY @sortExpression) AS Row FROM TempTable)
SELECT SAID, [Type], Region, ParentAccountName, AccountName, EEID, AMID, AM, FAEID, FAE FROM PagedList WHERE Row between @StartRowIndex and (@StartRowIndex+@maximumRows) - 1
END
March 12, 2009 at 2:28 pm
Wow thats some CTE :w00t:
I can't see anything obvious, and without the table structures and sample data, its difficult to help.
In ASP.NET I use the built-in sorting and paging facillities - as the data from the initial query will be cached, probably in a DataSet, you just need to keep track of PageIndex and SortExpression in session variables or something - no need for another round-trip to the database.
If for some reason that is not possible then I would debug the CTEs from the top down,
WITH cte1 AS ( ... ) SELECT * FROM cte1
.. if the data is what I expected then continue adding in the next cte
WITH cte1 AS ( ... ), cte2 AS ( ... ) SELECT * from cte2
Once you find which bit isn't giving the required results, you'll probably be able to post a simpler version of the problem, with some sample data. 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply