Please Help, this is driving me nuts !! 
Author Message
 Please Help, this is driving me nuts !!

Hello everyone,

The example below will return all records, but can this be done with TSQL ?

For example:


 BEGIN

 END

SELECT *
FROM Table1

I know this is not valid SQL but this is what I need. If 0 or NULL then
return all records.

I have tried different methods over the last couple of days without success.
Is this simply not done or not supported by TSQL ?

Thanks again,
Andrew.

----------------------------------------------------------------------------
-------
DROP Table Table1

CREATE TABLE Table1
 ([ID] varchar(10),
  [ProArea] int)
GO

INSERT INTO Table1 VALUES('1001',  '1')
INSERT INTO Table1 VALUES('1002',  '2')
INSERT INTO Table1 VALUES('1003',  '3')
INSERT INTO Table1 VALUES('1004',  '4')
GO

SELECT *
FROM Table1
WHERE ProArea = ProArea



Sun, 07 Aug 2005 17:40:04 GMT
 Please Help, this is driving me nuts !!

Not sure I quite follow what you are trying to do however if you are wanting to set proarea to ID where proarea is null or '0' then

update table1

set proarea=id

where proarea='0' or proarea is null

Br,

Mark.

--
Mark Broadbent MCSE+I, MCDBA

Quote:

> Hello everyone,

> The example below will return all records, but can this be done with TSQL ?

> For example:


>  BEGIN

>  END

> SELECT *
> FROM Table1

> I know this is not valid SQL but this is what I need. If 0 or NULL then
> return all records.

> I have tried different methods over the last couple of days without success.
> Is this simply not done or not supported by TSQL ?

> Thanks again,
> Andrew.

> ----------------------------------------------------------------------------
> -------
> DROP Table Table1

> CREATE TABLE Table1
>  ([ID] varchar(10),
>   [ProArea] int)
> GO

> INSERT INTO Table1 VALUES('1001',  '1')
> INSERT INTO Table1 VALUES('1002',  '2')
> INSERT INTO Table1 VALUES('1003',  '3')
> INSERT INTO Table1 VALUES('1004',  '4')
> GO

> SELECT *
> FROM Table1
> WHERE ProArea = ProArea



Sun, 07 Aug 2005 17:13:24 GMT
 Please Help, this is driving me nuts !!

Quote:

>  BEGIN

>  END

> SELECT *
> FROM Table1

> I know this is not valid SQL but this is what I need. If 0 or NULL then
> return all records.

> I have tried different methods over the last couple of days without
success.
> Is this simply not done or not supported by TSQL ?

SELECT columnList
    FROM Table1
    WHERE ProArea = CASE


    END

BTW, it's helpful if you use a more descriptive subject.  I bet just about
everyone's problems drive them nuts... and if everyone put 'please help' in
the subject line, well we know what would happen then...

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.



Sun, 07 Aug 2005 17:16:46 GMT
 Please Help, this is driving me nuts !!
Thanks very much for the help.

Andrew.

**I'll be more careful with the subject line in future.



Quote:

> >  BEGIN

> >  END

> > SELECT *
> > FROM Table1

> > I know this is not valid SQL but this is what I need. If 0 or NULL then
> > return all records.

> > I have tried different methods over the last couple of days without
> success.
> > Is this simply not done or not supported by TSQL ?

> SELECT columnList
>     FROM Table1
>     WHERE ProArea = CASE


>     END

> BTW, it's helpful if you use a more descriptive subject.  I bet just about
> everyone's problems drive them nuts... and if everyone put 'please help'
in
> the subject line, well we know what would happen then...

> --
> Aaron Bertrand, SQL Server MVP
> http://www.aspfaq.com/

> Please reply in the newsgroups, but if you absolutely
> must reply via e-mail, please take out the TRASH.



Sun, 07 Aug 2005 18:28:33 GMT
 Please Help, this is driving me nuts !!
Aaron,

I have played around with this a bit recently, and I got the impression that
the code you propose will always cause the Query optimizer to use a index
seek instead of a table scan.

Something similar like:
SELECT columnList
    FROM Table1
    WHERE ProArea Like CASE


    END
Also seems to cause an index seek under all circumstances.


Haven't checked it in a stored procedure yet to see if parameter sniffing
influences the results.

This sounds trivial (who's going to return all the rows from a million row
table?) but if you are going to return the aggregates from that million row
table it will make a difference.
Any thoughts/comments?

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.



Quote:

> >  BEGIN

> >  END

> > SELECT *
> > FROM Table1

> > I know this is not valid SQL but this is what I need. If 0 or NULL then
> > return all records.

> > I have tried different methods over the last couple of days without
> success.
> > Is this simply not done or not supported by TSQL ?

> SELECT columnList
>     FROM Table1
>     WHERE ProArea = CASE


>     END

> BTW, it's helpful if you use a more descriptive subject.  I bet just about
> everyone's problems drive them nuts... and if everyone put 'please help'
in
> the subject line, well we know what would happen then...

> --
> Aaron Bertrand, SQL Server MVP
> http://www.aspfaq.com/

> Please reply in the newsgroups, but if you absolutely
> must reply via e-mail, please take out the TRASH.



Sun, 07 Aug 2005 18:06:17 GMT
 Please Help, this is driving me nuts !!

Quote:
> This sounds trivial (who's going to return all the rows from a million row
> table?) but if you are going to return the aggregates from that million
row
> table it will make a difference.
> Any thoughts/comments?

I hadn't really investigated it from a performance frame of mind.  I don't
know that there is a straightforward solution that also yields the best
possible performance in all cases.

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.



Sun, 07 Aug 2005 18:09:59 GMT
 Please Help, this is driving me nuts !!
OR constructs are a real weakness of the optimizer.  Many times, an OR can
be rewritten as a UNION of two similar queries.  The optimizer then runs
each query separately and if the query is properly indexed uses a seek
rather than a scan.  Combining the results is often cheaper than a table or
index scan using an OR.  Again, the table structure, size, and indexing
layout will determine whether this is a good strategy.

--
Geoff N. Hiten
Senior Database Administrator
Careerbuilder.com



Quote:
> > This sounds trivial (who's going to return all the rows from a million
row
> > table?) but if you are going to return the aggregates from that million
> row
> > table it will make a difference.
> > Any thoughts/comments?

> I hadn't really investigated it from a performance frame of mind.  I don't
> know that there is a straightforward solution that also yields the best
> possible performance in all cases.

> --
> Aaron Bertrand, SQL Server MVP
> http://www.aspfaq.com/

> Please reply in the newsgroups, but if you absolutely
> must reply via e-mail, please take out the TRASH.



Sun, 07 Aug 2005 19:02:00 GMT
 Please Help, this is driving me nuts !!
Right... though I'd be interested in seeing a UNION approach to this
specific problem.  This is not a simple change from:

SELECT * FROM table WHERE x = 1 or x = 2

to

SELECT * FROM table WHERE x = 1
UNION
SELECT * FROM table WHERE x = 2

... this is a case where you only want one side of the UNION to return
results, depending on whether the parameter was NULL/0.

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.



Quote:
> OR constructs are a real weakness of the optimizer.  Many times, an OR can
> be rewritten as a UNION of two similar queries.  The optimizer then runs
> each query separately and if the query is properly indexed uses a seek
> rather than a scan.  Combining the results is often cheaper than a table
or
> index scan using an OR.  Again, the table structure, size, and indexing
> layout will determine whether this is a good strategy.



Sun, 07 Aug 2005 19:10:21 GMT
 Please Help, this is driving me nuts !!
Just a stab at it that I can check because I don't have SQL at home:


UNION

Btw, another thing I can't check at the moment. I think both the "ProArea =


ProArea if you want to return all rows.



Quote:
> Right... though I'd be interested in seeing a UNION approach to this
> specific problem.  This is not a simple change from:

> SELECT * FROM table WHERE x = 1 or x = 2

> to

> SELECT * FROM table WHERE x = 1
> UNION
> SELECT * FROM table WHERE x = 2

> ... this is a case where you only want one side of the UNION to return
> results, depending on whether the parameter was NULL/0.

> --
> Aaron Bertrand, SQL Server MVP
> http://www.aspfaq.com/

> Please reply in the newsgroups, but if you absolutely
> must reply via e-mail, please take out the TRASH.



> > OR constructs are a real weakness of the optimizer.  Many times, an OR
can
> > be rewritten as a UNION of two similar queries.  The optimizer then runs
> > each query separately and if the query is properly indexed uses a seek
> > rather than a scan.  Combining the results is often cheaper than a table
> or
> > index scan using an OR.  Again, the table structure, size, and indexing
> > layout will determine whether this is a good strategy.



Sun, 07 Aug 2005 22:11:13 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. This query is driving me nuts - please help !!

2. adErrInvalidArgument driving me nuts! please advise

3. HELP: isql is driving me nuts!

4. help vb & access2k is driving me nuts

5. HELP - ADO data Environment driving me nuts!

6. msdn docs drive me nuts, help find CDaoTableDef connect string format description

7. Help: Find mode is driving me nuts.

8. I am having a problem with a hard disk drive or floppy disk drive

9. please help - going nuts with DSNless connection

10. SQL 7.0 Backup/Restore , Maybe I am nuts

11. Am I nuts?

12. Am I nuts or......


 
Powered by phpBB® Forum Software