Dynamic query or really long If Statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic query or really long If Statement?

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2002-06-18 : 17:00:00

I have kind of a long sproc that is used to select the list of threads for a forum system similiar to this one. It is pretty complex because it handles pagination and ordering by a user selectable column either ascending or descending.

The code below is most of the proc that I am currently using. I have never liked how I have to write the entire insert statement twice within the proc to allow the user to order by a particular column ascending or descending. Now I want to add the following additional where clause only when a variable called @gen is not equal to 0.

Currently I have:
WHERE fr.forum_id = @forum_id AND latest_post_date > DATEADD(dd,-@date_filter,GETDATE())

But only when @gen <> 0 I want to have:
WHERE fr.forum_id = @forum_id AND latest_post_date > DATEADD(dd,-@date_filter,GETDATE()) AND fr.generation = @gen

So there are basically 4 different queries depending on the combination of @gen and @order
1) gen = 0 and @order = 'asc'
2) gen = 0 and @order = 'desc'
3) gen <> 0 and @order = 'asc'
4) gen <>0 and @order = 'desc'

Would I be better off just going back to a dynamic query, using a long If statement or is there some simple solution I am missing here?



CREATE TABLE [dbo].[#forum_temp]
(
 [temp_id] [int] IDENTITY (1, 1) NOT NULL,
 [root_id] [int])

IF LOWER(@order) = 'asc'
 BEGIN
    INSERT INTO [dbo].[#forum_temp]
    (
     root_id
    )
	SELECT
	 fr.root_id
	FROM forum_root fr
	WHERE fr.forum_id = @forum_id
		AND latest_post_date > DATEADD(dd,-@date_filter,GETDATE())
	ORDER BY CASE 
		WHEN @col = 'subject' THEN fr.subject
		WHEN @col = 'gen' THEN RIGHT('0'+convert(varchar, fr.generation), 2)
		WHEN @col = 'nickname' THEN fr.nickname
		WHEN @col = 'replies' THEN RIGHT('000000'+convert(varchar, fr.num_children_absolute), 6) 
		WHEN @col = 'views' THEN RIGHT('000000'+convert(varchar, fr.views), 6) 
		WHEN @col = 'thread start date' THEN CONVERT(varchar(30),fr.thread_start_date,121) 
		ELSE CONVERT(varchar(30),fr.latest_post_date,121) 
		END ASC, fr.thread_start_date DESC
 END
ELSE
 BEGIN
    INSERT INTO [dbo].[#forum_temp]
    (
     root_id
    )
	SELECT
	 fr.root_id
	FROM forum_root fr
	WHERE fr.forum_id = @forum_id
		AND latest_post_date > DATEADD(dd,-@date_filter,GETDATE())
	ORDER BY CASE 
		WHEN @col = 'subject' THEN fr.subject
		WHEN @col = 'gen' THEN RIGHT('0'+convert(varchar, fr.generation), 2) 
		WHEN @col = 'nickname' THEN fr.nickname
		WHEN @col = 'replies' THEN RIGHT('000000'+convert(varchar, fr.num_children_absolute), 6) 
		WHEN @col = 'views' THEN RIGHT('000000'+convert(varchar, fr.views), 6) 
		WHEN @col = 'thread start date' THEN CONVERT(varchar(30),fr.thread_start_date,121) 
		ELSE CONVERT(varchar(30),fr.latest_post_date,121) 
		END DESC, fr.thread_start_date DESC
 END

SELECT fr.root_id
        ...
	,fr.generation
FROM [dbo].[#forum_temp] temp_tbl
LEFT JOIN forum_root fr ON temp_tbl.root_id = fr.root_id
WHERE temp_tbl.temp_id BETWEEN (@page_size * (@page_num - 1))+1 AND (@page_size * @page_num)
ORDER BY temp_tbl.temp_id ASC

Thanks,
Chris

Edited by - cbrinson on 06/18/2002 17:01:11

cbrinson
Starting Member

36 Posts

Posted - 2002-06-19 : 13:52:00

Just kicking this back up one time to see if anyone has any ideas. Maybe the description was too long. Here is a short version. If you need a proc that contains a select statement that is to be ordered by a column dynamically, sorted asc or desc dynamically and also have a dynamic where clause, is there an easier or better way to do this other than having a long If statement?

Thanks again,
Chris

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-19 : 15:22:33

I'm a little confused because the only place I see this @gen variable is in your problem statement and not in your code, so I am not altogether clear on which WHERE clause your are having trouble with ...

That doesn't stop me from taking a shot in the dark though


...
WHERE
    fr.forum_id = @forum_id AND
    latest_post_date > DATEADD(dd,-@date_filter,GETDATE()) AND
    fr.generation = case when @gen <> 0 then @gen else fr.generation end

<O>

Edited by - Page47 on 06/19/2002 15:23:20

cbrinson
Starting Member

36 Posts

Posted - 2002-06-19 : 15:49:47

I had not made an attempt to add the @gen variable to my code yet since the only way I could think to do it was have an If statement with 4 different conditions. ie.

@gen <> 0 and @order = 'desc'
@gen <> 0 and @order = 'asc'
@gen = 0 and @order = 'desc'
@gen = 0 and @order = 'asc'

So when @gen = 0 the where statement will effectively be:

WHERE
fr.forum_id = @forum_id AND
latest_post_date > DATEADD(dd,-@date_filter,GETDATE()) AND
fr.generation = fr.generation

That works. Thanks! There is no way around having the IF statement that tests whether the select should be ordered ASC or DESC is there? I got errors when I tried to tack an IF statement or CASE statement that tests the value of @order right after my CASE statement that tests the value of @orderby.

One last question and I will leave you alone. Would the following WHERE clause be likely to cause any strange behavior?

ORDER BY CONVERT(varchar(30),fr.latest_post_date,121) DESC
,fr.latest_post_date DESC

The reason that 2nd order by is there is because for all the other @orderby cases such as the # of views, replies etc., I want to make sure that if two threads have the same number of views that the thread with the most recent reply appears first. But when ordering by latest_post_date that 2nd order by criteria is redundant. I just want to make sure that should not cause any strange results. I have not seen any so far though.

Thanks,
Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources