sql-server


SQL Multi-condition WHERE clause with one optional parameter?


Reworking an existing Stored Procedure to make a previously required parameter optional.
The query is massive, and deals with much more than my current issue, so I won't post it in it's entirety but I believe I can get my fix by editing one of the WHERE clauses.
WHERE
O.wsID = #wsid AND
((M.UpdateDate BETWEEN #olddate AND #newdate) OR (MI.UpdateDate BETWEEN #olddate AND #newdate))
The first parameter #wsid is the value I need to make optional, it's initially declared as 0 and given a new value from a select box on the front-end of the website. I have attempted to use a CASE statement to alternate between to WHERE clauses depending on whether the value is 0 or an actual ID.
WHERE
#wsid = CASE
WHEN #wsid <> 0 THEN ((O.wsID = #wsid) AND ((M.UpdateDate BETWEEN #olddate AND #newdate) OR (MI.UpdateDate BETWEEN #olddate AND #newdate)))
ELSE ((M.UpdateDate BETWEEN #olddate AND #newdate) OR (MI.UpdateDate BETWEEN #olddate AND #newdate)))
END
But I get a bunch of syntax errors in the WHERE clause itself and also an error stating Expecting CONVERSATION or TRY for the SELECT statement below.
Is there an easier option for making the O.wsid = #wsid parameter optional?
WHERE
(O.wsID = #wsid OR #wsid = 0) AND
((M.UpdateDate BETWEEN #olddate AND #newdate) OR (MI.UpdateDate BETWEEN #olddate AND #newdate))

Related Links

SQL Server 2005 stored procedure error - Line numbers don't match management studio
Entity Framework: Max. number of “subqueries”?
SQL Server 2012 Filtered Index - Is the Where clause too complex
SQL Server management in VS2012
Msg 137, Level 15, State 2, Line 29 Must declare the scalar variable “#ACTIVE_STATUS”
How to Select Random Rows in a data table of C#?
sp_getapplock to synchronize concurrent access to in-memory tables
How can I use sys.sp_MSforeachtable with different schema?
T-SQL First_Value() - How can it return more than a single value?
Best practice on how to store geography
SQL Server Database Trigger Update different table on update
Column Store Indexes Update With table locking
How do I know a query plan is invalidated?
how to reset SQL Server sa password
sql server table schema export in excel file automate
Derived Column condition to get string between two Hyphens

Categories

HOME
visual-studio-2015
pug
ibm-watson-cognitive
sass
google-cloud-bigtable
erlang
windows-7
google-tag-manager
json-ld
checksum
webdav
console
propertygrid
rebol
snap.svg
bro
sudo
currency
aruco
iso
wysiwyg
sign
typeahead
zope
apache-karaf
quartus
atlassian-plugin-sdk
movies
azure-servicebus-queues
restfb
qsub
npm-install
cronet
switching
jenkins-job-dsl
owl-api
mangodb
roundcube
smart-mobile-studio
gettext
von-neumann
powershell-remoting
passenger
hashcat
es-shell
duktape
amd
mod-fcgid
worker
double-buffering
appstore-approval
goquery
tiddlywiki
dwarf
maintenance
pycurl
visual-studio-templates
atlassian-crowd
bacnet
dac
web-essentials
multinomial
license-key
asymptote
schtasks.exe
selendroid
response-headers
anti-patterns
xjc
pylearn
ctrlp
c++-actor-framework
viewflipper
jquery-autocomplete
errorprovider
dache
spdy
level
joox
chrome-for-android
bsod
layered
ienumerator
resgen
pep8
sequelpro
symfony-2.0
datarepeater
plone-funnelweb
text-services-framework
qtembedded
phpcrawl
ruby-debug
squeel
hardware-acceleration
winverifytrust
pci-bus
gears
cots

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App