SP CASE - SQL Server Forums

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

Transact-SQL (2000)

SP CASE

Author

Topic

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-02-11 : 05:03:00

What is wrong with this?

CREATE PROC forum_select
@fm int
AS
SET NOCOUNT ON
SELECT f.forum_id, f.forum_title, f.forum_body, f.forum_date, u.user_firstname, u.user_lastname
FROM forum f, users u

CASE @fm
WHEN IS NULL THEN WHERE f.forum_user = u.user_id AND forum_repliedto = 0 AND forum_visible = 1
ELSE WHERE f.forum_user = u.user_id AND forum_repliedto = @fm AND forum_visible = 1
END

ORDER BY forum_date DESC
GO

I want to have different where statements depending on the value @fm... but it will not work :( Any ideas?

=====================================
Why not try and do the impossible?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-11 : 05:48:57

Your SELECT and CASE are all intertwingled.


SELECT f.forum_id, f.forum_title, f.forum_body,
  f.forum_date, u.user_firstname, u.user_lastname
FROM forum f
INNER JOIN users u ON f.forum_user = u.user_id
WHERE forum_visible = 1
  AND forum_repliedto = COALESCE(@fm, 0)
ORDER BY forum_date DESC

Oh dear, the CASE disappeared.

Edited by - Arnold Fribble on 02/11/2002 05:49:23

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-02-11 : 06:02:07

The problem is that I need a SP to work with a session variable, thus, when the session timeouts and the page is redirected. the SP gives an errormsg because it has no value to go on. The SP actually overrides all other code therefore rendering my redirection useless unless I can provide the SP with a default value of ex. 0 so that it still can execute... Funky business..

Will your thing actually do that? :)

=====================================
Why not try and do the impossible?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-11 : 06:20:44

Yes, if @fm IS NULL then it returns rows where forum_repliedto = 0, which is what I think you were intending. I just got a bit carried away and factored out all the other comparisons in the CASE... at which point it wasn't needed anymore. If I'd concentrated on the just syntactic problems:


SELECT f.forum_id, f.forum_title, f.forum_body,
  f.forum_date, u.user_firstname, u.user_lastname
FROM forum f, users u
WHERE
  CASE WHEN @fm IS NULL THEN f.forum_user = u.user_id
      AND forum_repliedto = 0 AND forum_visible = 1
       ELSE f.forum_user = u.user_id
      AND forum_repliedto = @fm AND forum_visible = 1
  END
ORDER BY forum_date DESC

You'll notice that I've moved the WHEN: the simple CASE syntax doesn't work with NULL comparisons.
This should give the same result as the simplified version I posted before.
However, I think you should factor out the common predicates in the two cases, particularly the join condition. With f.forum_user = u.user_id inside the case, you're giving the query optimizer a hard time

Edit: The code here is wrong. Corrected in next message.

Edited by - Arnold Fribble on 02/11/2002 06:44:10

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-11 : 06:38:12

Oops!

The other thing you can't do with CASEs is try return a boolean.


SELECT f.forum_id, f.forum_title, f.forum_body,
  f.forum_date, u.user_firstname, u.user_lastname
FROM forum f, users u
WHERE
  CASE
    WHEN @fm IS NULL THEN
      CASE WHEN f.forum_user = u.user_id AND forum_repliedto = 0
          AND forum_visible = 1 THEN 1 ELSE 0 END
    WHEN f.forum_user = u.user_id AND forum_repliedto = @fm
        AND forum_visible = 1 THEN 1
    ELSE 0
  END = 1
ORDER BY forum_date DESC

This will run, but incredibly slowly.

Edited by - Arnold Fribble on 02/11/2002 06:42:30

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-02-11 : 07:00:44

Cheers mate, been really helpful :D
Thanks!

=====================================
Why not try and do the impossible?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources