An Opinion on Dynamic Sql, Paging and Sorting

Question

An Opinion on Dynamic Sql, Paging and Sorting

meichner

SSCertifiable

Points: 5563
More actions
August 15, 2008 at 10:37 am

#195907

I am a c# programming. I have a gridview on one of my screens that I need to page through as well as sort. Right now the table that provides the data for the gridview is empty, but soon will have about 150,000 rows in it.
In order to get the data, I created a stored procedure that uses Row_number() for paging and Case statements to determine the sort order.
While I was building this stored procedure I came across an article that stated that creating a procedure where you sort on different columns that performance would be better using dynamic sql then case statements.
Is the author of this article correct?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

It will depend on the dynamic SQL and the case statement methods used.

I would imagine that the author found a situation in which they are correct. However, this is not necessarily the case all the time. If you want to post the DDL for your table, some sample data, and your current procedure there will be some people (including myself) that will give you a hand getting this optimized for your situation.

I usually avoid dynamic T-SQL and have never come across a situation myself in which it was the "best" solution for paging in a web form.

meichner SSCertifiable Points: 5563 More actions · Answer 2

Here is a copy of my stored procedure. Hopefully it will make sense (I am kind of new at this).

Sorry about the formatting. I am not sure how to clean it up in order to make it easier to read.

Thanks

CREATE PROCEDURE dbo.SalesOrder_GET_ALL( @CustomerNumber Int

, @SortExpression VarChar(100)

, @StartRowIndex Int

, @MaximumRows Int)

As

Declare @StartRow Int

Begin

Set NoCount On

-- The Index sent to this procedure is Zero Based. We have to Increment that value by 1 --

Set @StartRow = @StartRowIndex + 1;

With SalesOrder_CTE As

(

Select SOID

, CustomerNumber

, PoNumber

, DateEntered

, EnteredBy

, PoIssuedDate

, ToComeLater

, ShowDetails

, CsrRepID

, CustomerAddressID

, RowStatus_Status

, RowVersion

, LastChangedDateTime

, OperID

, CompanyName

, CSRRepFirstName

, CSRRepLastName

, EnteredByFirstName

, EnteredByLastName

, Row_Number() Over (Order By Case When @SortExpression = 'PONumber' Then PoNumber End Asc,

Case When @SortExpression = 'DateEntered' Then Convert(VarChar(Max), DateEntered, 102) End Asc,

Case When @SortExpression = 'PoIssuedDate' Then Convert(VarChar(Max), PoIssuedDate, 102) End Asc,

Case When @SortExpression = 'CSRRepLastName' Then CSRRepLastName + CSRRepFirstName End Asc,

Case When @SortExpression = 'EnteredByLastName' Then EnteredByLastName + EnteredByFirstName End Asc,

Case When @SortExpression = 'PONumber DESC' Then PoNumber End Desc,

Case When @SortExpression = 'DateEntered DESC' Then Convert(VarChar(Max), DateEntered, 102) End Desc,

Case When @SortExpression = 'PoIssuedDate DESC' Then Convert(VarChar(Max), PoIssuedDate, 102) End Desc,

Case When @SortExpression = 'CSRRepLastName DESC' Then CSRRepLastName + CSRRepFirstName End Desc,

Case When @SortExpression = 'EnteredByLastName DESC' Then EnteredByLastName + EnteredByFirstName End Desc

) As RowNumber

From SalesOrders

Where CustomerNumber = @CustomerNumber

)