May 27, 2013 at 12:47 pm
Hi,
I need to fetch rows from a table in SP depending on input parameter value.
@var is an input parameter which i am passing
if @var = no owner assigned' then fetch where Name is null or Email is Null
if @var = 'all' then return all the rows
if @var = some name then return rows matching the parameter value
for example:
AND ((Name = CASE WHEN @var = 'no owner assigned' THEN null
WHEN @var = 'all' THEN Name
ELSE @var
END) OR (Email = CASE WHEN @var = 'no owner assigned' THEN '' END))
but the above query is not working for me. Please help!!
May 27, 2013 at 1:03 pm
When you say its not working do you mean it gives the wrong resukts? If so, what results do you get?
I don't think this is right:
OR (Email = CASE WHEN @var = 'no owner assigned' THEN '' END)
Shouldn't Email be matched to null? You need to cover the other options of @var too:
OR (Email = CASE WHEN @var = 'no owner assigned' THEN null ELSE Email END)
May 27, 2013 at 2:40 pm
Lucy08 (5/27/2013)
Hi,I need to fetch rows from a table in SP depending on input parameter value.
@var is an input parameter which i am passing
if @var = no owner assigned' then fetch where Name is null or Email is Null
if @var = 'all' then return all the rows
if @var = some name then return rows matching the parameter value
for example:
AND ((Name = CASE WHEN @var = 'no owner assigned' THEN null
WHEN @var = 'all' THEN Name
ELSE @var
END) OR (Email = CASE WHEN @var = 'no owner assigned' THEN '' END))
but the above query is not working for me. Please help!!
You are trying to compare things to NULL - it won't work; neither "NULL = something" nor "NULL <> something" can ever return true of fale. they both always return UNKNOWN which in a where clause condition is treated as FALSE.
It's fairly easy to change the code to cope with that; based on your text description starting from AND as you do it is quite simple:
AND ( (@var='no owner assigned' and (Name is NULL or EMAIL is null or EMAIL=''))
-- your text says go when EMAIL is NULL if @var is "no owner assigned"
-- but your code says go when EMAIL is '' instead
-- this code allows either
OR @var = 'all'
OR @var = Name
)
Tom
May 27, 2013 at 5:01 pm
Lucy08 (5/27/2013)
Hi,I need to fetch rows from a table in SP depending on input parameter value.
@var is an input parameter which i am passing
if @var = no owner assigned' then fetch where Name is null or Email is Null
if @var = 'all' then return all the rows
if @var = some name then return rows matching the parameter value
for example:
AND ((Name = CASE WHEN @var = 'no owner assigned' THEN null
WHEN @var = 'all' THEN Name
ELSE @var
END) OR (Email = CASE WHEN @var = 'no owner assigned' THEN '' END))
but the above query is not working for me. Please help!!
I don't believe that CASE statements are necessary. Using CASE statements in a WHERE clause works just fine but can get awfully messy and hard to read with a lot of duplication. Try something like this:
First make up some sample data:
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT NOT NULL
,[Project] NVARCHAR(100) NULL
,[AssignedTo] NVARCHAR(100) NULL
,[Email] NVARCHAR(100) NULL
,PRIMARY KEY([ID]))
INSERT INTO #TempTable
SELECT 1,'Delta','Jerry','jerry@test.com' UNION ALL
SELECT 2,'Alpha',NULL,'Alpha@test.com' UNION ALL
SELECT 3,'Quebec','Nancy',NULL UNION ALL
SELECT 4,'Foxtrot','Barbara','barbara@test.com' UNION ALL
SELECT 5,'Hotel',NULL,NULL UNION ALL
SELECT 6,'Yankee','O''Brien','OBrien@test.com' UNION ALL
SELECT 7,'Romeo','Jerry','jerry@test.com'
SELECT * FROM #TempTable
Then create this procedure (or you could just run it as a script if you prefer):
CREATE PROCEDURE dbo.GetProjectAssignments
@pInputString VARCHAR(100)
AS
BEGIN
/* Possible status values */
DECLARE @reqStatusVal INT
IF NULLIF(@pInputString,'') IS NULL
OR @pInputString = 'No owner assigned'
BEGIN
SET @pInputString = NULL
SET @reqStatusVal = 0
END
ELSE IF @pInputString = 'All'
SET @reqStatusVal = 1
ELSE
BEGIN
SET @reqStatusVal = 9
END
--just for testing
--select @reqStatusVal, @pInputString
SELECT
ID,
Project,
AssignedTo,
Email
FROM
#TempTable
WHERE
(@reqStatusVal = 0 AND (AssignedTo IS NULL OR Email IS NULL))
OR
(@reqStatusVal = 1)
OR
(@reqStatusVal = 9 AND AssignedTo = @pInputString)
END
GO
Finally, some usage examples:
EXEC dbo.GetProjectAssignments 'All' -- all rows
EXEC dbo.GetProjectAssignments 'No owner assigned' -- either name or email is null
EXEC dbo.GetProjectAssignments '' -- either name or email is null
EXEC dbo.GetProjectAssignments NULL -- either name or email is null
EXEC dbo.GetProjectAssignments 'Jerry' -- returns all rows with this name
EXEC dbo.GetProjectAssignments 'Barbara' -- returns all rows with this name
EXEC dbo.GetProjectAssignments 'O''Brien' -- returns all rows with this name
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy