Re: Using CASE statement to build different where clause

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Erland Sommarskog (sommar_at_algonet.se)
Date: 03/16/04 

Date: Tue, 16 Mar 2004 23:57:20 +0000 (UTC)

NWx (test@test.com) writes:
> Is it possible to use CASE statement to select a different where clause
> in a SQL statement?

There is no CASE statement in SQL Server, but there is a CASE expression.

> I want to create a stored proc, which can receive two parameters, start
> and end date
> If parameters are present (not null I suppose), I want to select records
> between those dates.
> Otherwise I want to select all records

WHERE date BETWEEN CASE WHEN @startdate IS NOT NULL
                        THEN @startdate
                        ELSE '19000101'
                   END AND
                   CASE WHEN @enddate IS NOT NULL
                        THEN @enddate
                        ELSE '20500101'
                   END

Or shorter:

WHERE date BETWEEN coalesce(@startdate, '19000101') AND
                   coalesce(@enddate, '20500101')

coalesce is in fact just a shortcut for the CASE expression above. 

-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Relevant Pages

  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I moved every table I was able to move to the SQL ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... communication between ODBC (OLEDB and Native Client, ...
    (microsoft.public.sqlserver.connect)
  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Re: Unable to Apply SP4 to SQL 2000 Cluster (new Node)
    ... Rebuild the node in the failover cluster. ... Scenario 1" in SQL Server 2000 Books Online. ... This setup process updates to SP4 only the binaries on the new ...
    (microsoft.public.sqlserver.clustering)
  • Re: WSS 3.0 question
    ... I followed the advise given in removing WSS 3.0 etc, ... the server is complaining that the SQL service(?) was tempered with or corrupt. ... I may just instal the SQL server as I was going eventuall use it anyway. ... If WSUS 3.0 is installed, I would suggest you uninstall it and then you install WSS 3.0. ...
    (microsoft.public.windows.server.sbs)
  • Re: SQL Server 2005 Cluster Setup Quiz
    ... I did test and it did not install the client tools. ... http://www.clusterhelp.com - Cluster Training ... Microsoft SQL Server MVP ... Provide a template on how to read SQL Server 2005 setup log files. ...
    (microsoft.public.sqlserver.clustering)