What is the problem with tbh_Forums_GetThreads Stored Procedure?

maratin · February 14th, 2010, 11:01 AM

Hi,
In chapter 8 it says that there are no tbh_Forums_GetThreads and tbh_Forums_GetThreadsByForum stored procedures;

This is because they must support different ordering (LastPostDate, ReplyCount, and ViewCount), and that we we can't parameterize the ORDER BY clause together with the ROW_NUMBER()... OVER statement.

Can you please explain what is the problem exactly?

Lee Dumond · February 14th, 2010, 12:58 PM

The problem is that in T-SQL, as stated in the book, you cannot pass in a parameter that can be consumed by an ORDER BY clause.

That means, to order the results based on various criteria, the command text has to be built on the fly so that the ORDER BY criteria can be changed dynamically.

If you look at the code behind, you will see a big long command text string there, that is dyanamically built in order to support ordering.

Since you are using this string to execute the query, there's no purpose in having stored procedures to do it.

maratin · February 14th, 2010, 06:02 PM

I don't see why I can't pass column name as string parameter and direction order as int parameter to the SP, and there using CASE, build appropriate querie, something like that:

Code:

.
.
.
 ROW_NUMBER() OVER (ORDER BY 
  CASE WHEN @OrderByColumn='LastPostDate' AND @OrderDirection=0 THEN tbh_Posts.LastPostBy END ASC,
  CASE WHEN @OrderByColumn='ReplyCount' AND @OrderDirection=0 THEN tbh_Posts.ReplyCount END ASC,
.
.
.

Lee Dumond · February 14th, 2010, 06:23 PM

You might be able to do that, but that isn't really a parameterized query, is it?. Also, it places a lot of UI logic in your sproc which doesn't rightly belong there.

In my opinion, the author's approach is the right one here. Not saying that is the only way to do it though.

maratin · February 14th, 2010, 07:00 PM

why it's not parameterized query?
the sort column is passed in a right way:

Code:

  cmd.Parameters.Add("@OrderByColumn", SqlDbType.NVarChar).Value = orderByColumn;
            cmd.Parameters.Add("@OrderDirection", SqlDbType.Int).Value = orderDirection;

and it's safe regarding sql injects. Isn't it?