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,
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply