CODEWITHSUNDEEP
sql-servert-sqlsortingstored-proceduressql-server-2012
SBB
SBB

Reputation: 8970

TSQL Sort Data not Working

I have a piece of a query that is used in a dynamic SQL statement. In this case, it is taking a list of selected fields, their orders, and sorting preference so creating the SORT statement.

-- Insert the data into the sort statements
INSERT into @sortStatements (sortStatement)
SELECT fo.fieldName + ' ' + b.FilterValues
FROM red.dbo.BS_ContentRequests_Tasks_User_Fields AS a
JOIN red.dbo.BS_ContentRequests_Tasks_Fields_Options AS fo
ON a.fieldID = fo.fieldID
JOIN (
    Select Main.fieldID, Left(Main.FilterValues,Len(Main.FilterValues)-1) As "FilterValues"
    From
    (
        Select distinct ST2.fieldID, 
        (
            Select ISNULL(ST1.[sortType], 'ASC') + ', ' AS [text()]
            From red.dbo.BS_ContentRequests_Tasks_User_Fields ST1
            JOIN dbo.BS_ContentRequests_Tasks_Fields_Options AS fl
            ON st1.fieldID = fl.fieldID
            Where ST1.fieldID = ST2.fieldID
            AND ST1.QID = @QID
            ORDER BY ST1.sortOrder ASC
            For XML PATH ('')
        ) [FilterValues]
        From red.dbo.BS_ContentRequests_Tasks_User_Fields ST2
        WHERE ST2.QID = @QID
    ) [Main]
) as b
ON a.fieldID = b.fieldID
WHERE a.QID = @QID

SELECT * FROM @sortStatements

-- Trim off the trailing comma
SELECT @sortClause = coalesce(@sortClause + ', ', '') + sortStatement
FROM @sortStatements

-- Set a variable to hold the whole WHERE logic which we will append later
SET @sort = (SELECT @sortClause)

SELECT @sort

The result set of sortStatements is below:

enter image description here

The final string that I use in my code (the result of @sort) is:

requestContentType ASC, dateRequested ASC, desiredCompletion ASC, dueDate ASC, requestID DESC, requestTitle ASC, requestType DESC, startDate ASC, requestStatus ASC, taskName ASC, requestorName ASC, requestorNTID ASC, taskStatus ASC, taskID ASC

The issue here is that those field names are not in the correct order that they should be in. In the middle statement, I have my order by clause which should be ordering it by the sortOrder

Here is the fieldName with its sort value, the order it should be in:

enter image description here

As you can see, the final output is in no particular order.. Is part of this query causing some type of sorting that is overriding my own? I have tried placing the order by clause in several areas but it doesn't change the final output.

Thoughts?

Upvotes: 0

Views: 48

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

Add the sortOrder to the generation of @sortStatements THEN you can

SELECT @sortClause = coalesce(@sortClause + ', ', '') + sortStatement
FROM @sortStatements
Order by sortOrder

Upvotes: 2

Related Questions