A better method for default set?

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  July 14, 2008 at 8:45 am

  #195208

  Hi All,

  I have a query, all code below, which works but I am convinced there is a better way.

  If you cun the code below and select *, you will see:

  DBCON DB1 Crispin

  DBCON DB2 Tarah

  DBCON DB3 NULL

  XXCON DB10 Crispin

  XXCON DB20 Tarah

  XXCON DB30 NULL

  If @Hostname = 'Crispin', I want all the Crispin's, if Tarah then.... if @Hostname = 'bob' or fred etc (Any unknown) I want the NULL hostnames.

  The attached Query works but makes two scans of the table and a NOT IN.

  If there a better option.

  Cheers,

  Crispin

  --drop TABLE #cp

  --

  --CREATE TABLE #CP (

  -- PropertyName VARCHAR(100),

  -- PropertyValue VARCHAR(100),

  -- HostName VARCHAR(100)

  --)

  --

  --INSERT INTO #CP VALUES ('DBCON', 'DB1', 'Crispin')

  --INSERT INTO #CP VALUES ('DBCON', 'DB2', 'Tarah')

  --INSERT INTO #CP VALUES ('DBCON', 'DB3', NULL)

  --

  --INSERT INTO #CP VALUES ('XXCON', 'DB10', 'Crispin')

  --INSERT INTO #CP VALUES ('XXCON', 'DB20', 'Tarah')

  --INSERT INTO #CP VALUES ('XXCON', 'DB30', NULL)

  DECLARE

  @HostName VARCHAR(100)

  --SET @HostName = 'Crispin'

  SET @HostName = 'Bob'

  SELECT

  *

  FROM

  #CP cp

  WHERE

  1 = CASE

  WHEN (@Hostname NOT IN (SELECT Hostname FROM #CP WHERE HostName IS NOT NULL) AND cp.HostName IS NULL) THEN 1

  WHEN cp.HostName = @HostName THEN 1

  ELSE 0

  END

  Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

• brdudley

  SSCrazy

  Points: 2320

  More actions

  July 14, 2008 at 10:58 am

  #842553

  Try this, it seems to get slightly better performance than your query. If your table isn't going to be any bigger than this, don't spend too much time optimizing it.

  Select *

  From #CP cp

  Where cp.HostName Is Null

  Or (Exists( select 1 from #CP where hostname = @Hostname)

  and cp.HostName = @HostName

  )