Whats the best way to do this...

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)

Whats the best way to do this...

Author

Topic

Kappy
Starting Member

30 Posts

Posted - 2002-03-21 : 10:21:44

Hi- I have a variable (@host) that may or may not be null. What I am trying to accomplish is basically this:

If the value of @host is null, then do a simple select * from table

If the value of @host isn't null, then do a select * from table where hostname = @host

The @host variable is being supplied by a webpage, and the results are being output to a webpage as well. I guess what I'm looking for is the best way to handle this. Should I just have the asp page process one query if @host is null and another if it isn't null? Or is there a way it can all be done in one statement?

Thanks!

Jay99

468 Posts

Posted - 2002-03-21 : 10:29:05

Under no circumstances should you put 'select * from...' code into production. Take the time to type out your field lists.

That being said . . .


select
    field1,
    field2
from
    table
where
    hostname = isnull(@host,hostname)

... should do the trick.

Jay
<O>

Kappy
Starting Member

30 Posts

Posted - 2002-03-21 : 10:36:00

Jay-

Thanks - that works perfectly!

And for the record, under no circumstances WOULD I use a select * statement in production... but it's a timesaver here...

Thanks again.

Jay99

468 Posts

Posted - 2002-03-21 : 10:52:33

quote:

Thanks - that works perfectly!

Great! Now the downside. If you have an index on table.hostname, the solution I provided will not use that index for a seek. Instead it will scan the clustered index.

You might get better performance out of ...


if @host is null
    select
        blah
    from
        table
else
    select
        blah
    from
        table
    where
        hostname = @hostname

A Hayabusa may be faster, but a Ducati 996 is more sexy . . . sometimes you just have to go for the Ducati :)

Jay
<O>

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-21 : 11:14:24

Kappy-

It isn't always true ( of course, what is! ) but generally your assertion that "select * is a timesaver" is false, false, false.

It's not a flexible statement, in the sense that it's difficult to leverage the results of the select in other DML you later write.

As an example, one construct that will haunt you every time is:
insert table [no field list]
select * from othertable

any changes to the # or positioning of columns within othertable immediately breaks the statement. in fact, if you try to publish othertable as part of a transactional replication scheme, it will warn you that this construct will break ( assuming it can find it, which it can't if you're using embedded sql ).

select * also explicitly assumes that every column is desired, for all time. Usually that's not really the case, unless you have a top-of-the-line crystal ball

. You might add a computed column, for example, or columns which contain sensitive information. The table might be "Employees", let's say, and you might be tasked with adding salary information and unfortunately forget about your select *.

you could end up a wanted man ...

setBasedIsTheTruepath
<O>

Kappy
Starting Member

30 Posts

Posted - 2002-03-21 : 13:42:53

setBasedIsTheTruepath -

Thanks for the info - very interesting! Though I have to tell you that when I said it was a timesaver, I was referring to typing the message I posted...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources