July 23, 2008 at 2:01 pm
I need to retrieve either a specific value or all records, depending if there is a match on the (integer) @parameter.
Overview:
Select Action from ActionList where ActionID = @ActionID
If ActionID finds a match then return the [Action], if no match - return all records.
Help! - TIA
July 23, 2008 at 2:13 pm
Here are a couple of T-SQL options:
[font="Courier New"]IF @ActionID IS NULL -- or @ActionId = [DefaultValue]
BEGIN
SELECT
*
FROM
dbo.Actions
END
ELSE
BEGIN
SELECT
*
FROM
dbo.Actions
WHERE
ActionID = @ActionId
END
-- OR
SELECT
*
FROM
dbo.Actions
WHERE
ActionId = CASE
WHEN @ActionID IS NULL THEN @ActionID-- or @ActionId = [DefaultValue]
ELSE ActionID
END[/font]
Is this in an SP or from an Application or Reporting Services? If you are doing AdHoc SQL from the client you could test the value and send the appropriate query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 23, 2008 at 2:16 pm
[font="Courier New"]Select Action from ActionList where ActionID = @ActionID
OR NOT EXISTS (SELECT TOP 1 X.Action FROM ActionList X WHERE X.ActionID = @ActionID)[/font]
That's not going to be very efficient though, because you have to check the table twice.
If you are doing this in a stored procedure, there are some other options.
July 23, 2008 at 3:23 pm
Tom Hamilton (7/23/2008)
I need to retrieve either a specific value or all records, depending if there is a match on the (integer) @parameter.Overview:
Select Action from ActionList where ActionID = @ActionID
If ActionID finds a match then return the [Action], if no match - return all records.
Help! - TIA
If I am understanding what you want, then this should work:
IF EXISTS (SELECT * FROM ActionList WHERE ActionID = @ActionID)
BEGIN;
SELECT Action FROM ActionList WHERE ActionID = @ActionID
END;
ELSE
BEGIN;
SELECT Action FROM ActionList;
END;
The above will return rows that match - else, return everything.
Now, if what you are really looking for is to be able to pass into the procedure an optional value, where if the parameter is passed - then return only those that match and if the parameter is not passed, then return all rows, then you could use the following:
CREATE PROCEDURE dbo.MySearch
@ActionID int = 0 -- I am assuming an integer value
AS
SELECT Action
FROM ActionList
WHERE ActionID = @ActionID
OR @ActionID = 0; --
GO
It really depends on what you are trying to accomplish.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 23, 2008 at 4:17 pm
Hi Jack - thank you for your answer - very close to what I need. I'm building a stored procedure to populate a control based on another control.
I'm not using any null parameters - there will always be some value to test
If ActionID = @ActionID Then return scalar) [Action]
else
(if no match) Then return [All Actions (list of 30 choices)]
I know this must be close but I just can't quite see the solution
July 23, 2008 at 4:23 pm
Thank you Jeffrey - the middle solution fit right into my stored procedure and returns exactly what I needed. 🙂
July 23, 2008 at 4:29 pm
Thanks Jack
July 23, 2008 at 4:33 pm
See if this test code helps you.
create table #TestTable (
ActionVal int
);
insert into #TestTable (ActionVal)
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8;
declare @val int;
set @val = 9;
select
*
from
#TestTable
where
exists(select 1 from #TestTable where ActionVal = @val)
or not exists(select 1 from #TestTable where ActionVal = @val);
set @val = 1;
select
*
from
#TestTable
where
(exists(select 1 from #TestTable where ActionVal = @val) and
ActionVal = @val)
or not exists(select 1 from #TestTable where ActionVal = @val);
drop table #TestTable;
😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply