April 30, 2009 at 12:52 pm
Ok. I'm relatively a total n00b at SQL Server, my strength is more web based languages such as C# or PHP
What I'm attempting is, I have several stored procedures that have parameters of varchar datatypes. I can choose whether or not to pass values into these params, and they will default to either NULL or '%'. I also have some uniqueidentifer params and I want to do something similar to them that I do for the character params. So it goes a bit like this
-------------------------------------------------------------
@VarcharParam1 varchar(50)=null
@VarcharParam2 varchar(50)='%'
@UIDParam uniqueidentifier=null
SELECT * FROM MyTable
WHERE COALESCE(VarcharCol1,'') LIKE
case when @VarcharParam1 IS NULL then '%'
else @VarcharParam1 end
AND COALESCE(VarcharCol2,'') LIKE @VarcharParam2
-------------------------------------------------------------
-- The varcharParams here are just an example of 2 different methods of doing this. both work fine, obviously one is a bit more long winded
I want to do something similar with the uniqueidentifier but I'm unsure how to go about achieving this without casing the whole thing and repeating the query in an else section adding the GUID to the where clause of the ELSE case
-------------------------------------------------------------
(like)
IF @UIDParam IS NULL
--Do select without the GUID Col = @UIDParam
ELSE
--Do select with the GUID Col = @UIDParam
-------------------------------------------------------------
Yuk! That is so horible. Imagine having more than 1 uniqueid param. Then everything gets really confusing
So ideally I want to go something like this
-------------------------------------------------------------
SELECT * FROM MyTable
WHERE COALESCE(VarcharCol1,'') LIKE
case when @VarcharParam1 IS NULL then '%'
else @VarcharParam1 end
AND COALESCE(VarcharCol2,'') LIKE @VarcharParam2
AND GUIDColumn LIKE
case when @UIDParam IS NULL then ??? else @UIDParam end
-------------------------------------------------------------
Obviously that doesn't work because I'm not sure there is a wildcard for uniqueidentifiers like there is for varchar fields.
Anyone got any ideas? Am I making any sense here?
Kind Regards,
Joel
April 30, 2009 at 1:16 pm
Rahah! I've got it. Get rid of NULL and do 0x00
CREATE PROCEDURE SampleProc
@a uniqueidentifier,
@b-2 uniqueidentifier,
@c uniqueidentifier
AS
-- convert NULL parameters to 'empty' values
-- NULLs cannot be correctly compared using this pattern
SET @a = ISNULL(@a, 0x00)
SET @b-2 = ISNULL(@b, 0x00)
SET @c = ISNULL(@c, 0x00)
SELECT a.col, b.col, c.col
FROM a inner join b inner join c
WHERE
CASE WHEN @a = 0x00
THEN @a
ELSE a
END = @a
AND
CASE WHEN @b-2 = 0x00
THEN @b-2
ELSE b
END = @b-2
AND
CASE WHEN @c = 0x00
THEN c
ELSE @c
END = @c
April 30, 2009 at 1:22 pm
Hi
Usually you should avoid functions in the WHERE clause to ensure better index usage.
What about OR? Try this:
DECLARE @t TABLE (Id INT NOT NULL IDENTITY, AnyGuid UNIQUEIDENTIFIER)
INSERT INTO @t
SELECT NEWID()
UNION ALL SELECT NEWID()
UNION ALL SELECT NEWID()
DECLARE @guid UNIQUEIDENTIFIER
-- Get a sample value
SELECT TOP(1)
@guid = AnyGuid
FROM @t
-- Query a specified guid
SELECT *
FROM @t
WHERE @guid IS NULL OR AnyGuid = @guid
-- Set NULL
SELECT @guid = NULL
-- Query all others
SELECT *
FROM @t
WHERE @guid IS NULL OR AnyGuid = @guid
Greets
Flo
April 30, 2009 at 1:46 pm
This may be of interest to you.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 30, 2009 at 1:48 pm
Brilliant
If only I would have known all this before. Probably would have saved some hair
April 30, 2009 at 1:59 pm
GilaMonster (4/30/2009)
This may be of interest to you.http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Hi Gail
Also thanks a lot from my side! 🙂
Greets
Flo
May 1, 2009 at 1:51 am
This is really good stuff. Thank you all for being so helpful
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply