May 10, 2010 at 4:38 pm
Greetings all,
See script below at bottom of post.
I have a view which currently selects all from table [IDs].
SELECT ID FROM IDs
In certain cases I want to call the same view to select all from table IDs only if the ID also exists in table List.
These different cases are determined by a value in a parameter table. My parameter value will be either 'All' or 'List'
If I was doing this in a procedure I would use if logic
DECLARE @myparam char(6)
SET myparam = (SELECT Value FROM Parameters WHERE Parameter = 'Export')
IF myparam = 'All'
BEGIN
SELECT ID FROM IDs
END
ELSE
BEGIN
SELECT ID FROM IDs WHERE ID IN (SELECT ID FROM LIST)
END
Can I do this type of IF/ELSE logic in the WHERE clause of a view?
Thanks to anyone who can help.
CREATE TABLE IDs
(ID int)
INSERT INTO IDs
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6
CREATE TABLE List
(ID int)
INSERT INTO List
SELECT 3
CREATE TABLE Parameters
(
Parameter varchar(6),
Value varchar(6)
)
INSERT INTO Parameters
SELECT 'Export','All'
SELECT * FROM IDs
SELECT * FROM List
SELECT * FROM Parameters
DROP TABLE IDs
DROP TABLE List
DROP TABLE Parameters
May 11, 2010 at 3:02 am
Is this what you need?
----
----
where
((SELECT Value FROM Parameters WHERE Parameter = 'Export') = 'all')
or (id in (SELECT ID FROM LIST))
- arjun
https://sqlroadie.com/
May 11, 2010 at 9:00 am
Arjun Sivadasan (5/11/2010)
Is this what you need?
yes, thank you so much.
May 11, 2010 at 11:22 pm
🙂 you are welcome.
-arjun
https://sqlroadie.com/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply