Can I use the 'IF' clause in a 'WHERE' clause ?

Background:
I am developing a website using MS Visual Studio 2005 and SQL 2005 Express.
I'm using SQL queries (not stored procedures yet).
Problem:
I have a query that I can't make work. I found the COALESCE function and it
solves part of the problem but not all.
I have a variable '@.filter_by_date' which is bound to a dropdownlist on the
webpage. The dropdownlist's values are NULL, -7, -14, -30 which represent th
e
number of days to subtract from today's date.
If a user selects the NULL option the query should return all rows which is
how my query is:
SELECT * FROM t_jobs WHERE (created_date >= COALESCE(@.filter_by_date,
created_date))
The problem arises when the user selects any of the other options. If the
user selects -7, for example, then this value should be subtracted from the
current date and used in the query to only show jobs created after that date
.
The query looks like this:
SELECT * FROM t_jobs WHERE (created_date >= GETDATE() -@.filter_by_date)
Is it possible to merge the two queries? I thought of trying to use an 'IF'
in the 'WHERE' clause but I can't get it to work.
Thank you for your time. Hope you can help.You'll probably get better performance with two separate queries, each in
its own stored proc. However, you can try:
SELECT
*
FROM
t_jobs
WHERE
@.filter_by_date IS NULL
OR (@.filter_by_date IS NOT NULL
AND created_date >= GETDATE() -@.filter_by_date))
(assumes @.filter_by_date is a positive number.)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
news:6AA25C85-C8B9-48FB-8AE6-8850E0A03161@.microsoft.com...
> Background:
> I am developing a website using MS Visual Studio 2005 and SQL 2005
> Express.
> I'm using SQL queries (not stored procedures yet).
> Problem:
> I have a query that I can't make work. I found the COALESCE function and
> it
> solves part of the problem but not all.
> I have a variable '@.filter_by_date' which is bound to a dropdownlist on
> the
> webpage. The dropdownlist's values are NULL, -7, -14, -30 which represent
> the
> number of days to subtract from today's date.
> If a user selects the NULL option the query should return all rows which
> is
> how my query is:
> SELECT * FROM t_jobs WHERE (created_date >= COALESCE(@.filter_by_date,
> created_date))
> The problem arises when the user selects any of the other options. If the
> user selects -7, for example, then this value should be subtracted from
> the
> current date and used in the query to only show jobs created after that
> date.
> The query looks like this:
> SELECT * FROM t_jobs WHERE (created_date >= GETDATE() -@.filter_by_date)
> Is it possible to merge the two queries? I thought of trying to use an
> 'IF'
> in the 'WHERE' clause but I can't get it to work.
> Thank you for your time. Hope you can help.
>|||Thanks for the help. I implemented your querry into mine but when I go out o
f
and back into the Query Builder the query has changed. My other comparisons
in the WHERE clause have been duplicated for each side of the OR operator. I
s
this normal?
"Tom Moreau" wrote:

> You'll probably get better performance with two separate queries, each in
> its own stored proc. However, you can try:
> SELECT
> *
> FROM
> t_jobs
> WHERE
> @.filter_by_date IS NULL
> OR (@.filter_by_date IS NOT NULL
> AND created_date >= GETDATE() -@.filter_by_date))
> (assumes @.filter_by_date is a positive number.)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
> news:6AA25C85-C8B9-48FB-8AE6-8850E0A03161@.microsoft.com...
>
>|||Don't use the query builder. Use Query Analyzer (QA).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
news:A944E1F3-C855-441C-8F90-F4ED600E33F1@.microsoft.com...
> Thanks for the help. I implemented your querry into mine but when I go out
> of
> and back into the Query Builder the query has changed. My other
> comparisons
> in the WHERE clause have been duplicated for each side of the OR operator.
> Is
> this normal?
> "Tom Moreau" wrote:
>|||Thank you for your prompt replies, much appreciated.
Ok, stupid question but here goes. Is QA in SQL Server? I'm not using SQL
Server yet, waiting until my Visual Studio 2005 Pro turns up (any day now)
before I dable into yet another app.
Mat
"Tom Moreau" wrote:

> Don't use the query builder. Use Query Analyzer (QA).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
> news:A944E1F3-C855-441C-8F90-F4ED600E33F1@.microsoft.com...
>
>|||If you have SQL Server 2000, that's part of the tools that ship with it. If
you have SQL Server 2005, then you use SQL Server Management Studio (SSMS),
that ships with SQL Server 2005.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
news:476B52A3-1188-46DE-AF3F-B905475B1A1D@.microsoft.com...
Thank you for your prompt replies, much appreciated.
Ok, stupid question but here goes. Is QA in SQL Server? I'm not using SQL
Server yet, waiting until my Visual Studio 2005 Pro turns up (any day now)
before I dable into yet another app.
Mat
"Tom Moreau" wrote:

> Don't use the query builder. Use Query Analyzer (QA).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
> news:A944E1F3-C855-441C-8F90-F4ED600E33F1@.microsoft.com...
>
>

Can I recover?

An update statement was made against a table that did not include a 'where'
clause and hence all the records were updated and basically destroying all
the data.
There is no recent backup of the database ( the last one was in February).
Is there anyway to get back to the the way the table was before the update?
Thanks for any help,
Patrick
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--> There is no recent backup of the database ( the last one was in February).
WHOOPS!

> Is there anyway to get back to the the way the table was before the
> update?
http://www.aspfaq.com/2449|||"Patrick Nolan" <nolanpa@.bcsd.com> wrote in message
news:1126123461_16397@.spool6-east.superfeed.net...
> An update statement was made against a table that did not include a
> 'where'
> clause and hence all the records were updated and basically destroying all
> the data.
> There is no recent backup of the database ( the last one was in February).
> Is there anyway to get back to the the way the table was before the
> update?
> Thanks for any help,
> Patrick
>
Ouch!!!
You can purchase one of several well-known products that Aaron listed, or if
you know about the time that the update took place *AND* you have
transaction logs, then you can do a Point in Time restore.
Check the BOL for specifics on this.
Rick Sawtell
MCT, MCSD, MCDBA|||It depends on the recovery model you're using. If you're using the full
recovery model, then you can probably use a log reader. If you're using the
simple recovery model, then you might be out of luck.
"Patrick Nolan" <nolanpa@.bcsd.com> wrote in message
news:1126123461_16397@.spool6-east.superfeed.net...
> An update statement was made against a table that did not include a
'where'
> clause and hence all the records were updated and basically destroying all
> the data.
> There is no recent backup of the database ( the last one was in February).
> Is there anyway to get back to the the way the table was before the
update?
> Thanks for any help,
> Patrick
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
=--|||Rick..how is your solution accomplished? I did not think this was possible
without a DB backup. I just want to understand in case I ever need it too.
Thanks..
"Rick Sawtell" wrote:

> "Patrick Nolan" <nolanpa@.bcsd.com> wrote in message
> news:1126123461_16397@.spool6-east.superfeed.net...
> Ouch!!!
> You can purchase one of several well-known products that Aaron listed, or
if
> you know about the time that the update took place *AND* you have
> transaction logs, then you can do a Point in Time restore.
> Check the BOL for specifics on this.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:644AA6A1-220F-4A59-84AE-70FA438E6157@.microsoft.com...
> Rick..how is your solution accomplished? I did not think this was
> possible
> without a DB backup. I just want to understand in case I ever need it
> too.
> Thanks..
> "Rick Sawtell" wrote:
>
Check the BOL for:
RESTORE LOG ... WITH STOPAT
Rick Sawtell
MCT, MCSD, MCDBA|||Rick, thanks, but I am still . Looked at the STOPAT, but still
thought a DB restore would be needed first, which was indicated by BOL. Can
the restore log command be accomplished without restoring a DB backup? Just
trying to learn all I can. thanks again..
"Rick Sawtell" wrote:

> "NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
> news:644AA6A1-220F-4A59-84AE-70FA438E6157@.microsoft.com...
> Check the BOL for:
> RESTORE LOG ... WITH STOPAT
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Maybe you should also look into preventing such... khm... situations.
For instance - doing it step by step (in transaction):
begin tran
-- do your modifications here
-- check results
-- then either:
rollback tran -- if something went wrong
-- or:
commit tran -- if all is well in the world of data storage
ML
p.s. oh, and yeah - backup.|||If the one that you made on Feb was a Full Database Backup and the recovery
mode has been setup with "Full", I believe you can backup the current
Transaction Log and then you can still do a "Point In Time" restore.
Ed
"Patrick Nolan" wrote:

> An update statement was made against a table that did not include a 'where
'
> clause and hence all the records were updated and basically destroying all
> the data.
> There is no recent backup of the database ( the last one was in February).
> Is there anyway to get back to the the way the table was before the update
?
> Thanks for any help,
> Patrick
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News=
=--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||> Rick, thanks, but I am still . Looked at the STOPAT, but still
> thought a DB restore would be needed first, which was indicated by BOL.
Correct. So your only option is if the db has been in full recovery mode sin
ce your last db backup
(not likely) and you now do a log backup. Or use a log reader tool. Some add
itional info at
http://www.karaszi.com/SQLServer/in...eral_times.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:DA40AC62-F241-4356-A5EB-83B83ABF80F9@.microsoft.com...
> Rick, thanks, but I am still . Looked at the STOPAT, but still
> thought a DB restore would be needed first, which was indicated by BOL. C
an
> the restore log command be accomplished without restoring a DB backup? Ju
st
> trying to learn all I can. thanks again..
> "Rick Sawtell" wrote:
>