July 14, 2008 at 8:45 am
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:
DBCONDB1Crispin
DBCONDB2Tarah
DBCONDB3NULL
XXCONDB10Crispin
XXCONDB20Tarah
XXCONDB30NULL
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 (
--PropertyNameVARCHAR(100),
--PropertyValueVARCHAR(100),
--HostNameVARCHAR(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
@HostNameVARCHAR(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!
July 14, 2008 at 10:58 am
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
Wherecp.HostName Is Null
Or(Exists( select 1 from #CP where hostname = @Hostname)
and cp.HostName = @HostName
)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply