IF WHERE CASE ON VIEW - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

IF WHERE CASE ON VIEW

Author

Topic

JohanDP
Starting Member

2 Posts

Posted - 2011-05-27 : 07:31:17

Dear SQL Experts,

I want to create a view in MS SQL Server 2008
that displays per user/ usergroup another table/view with IF/ELSE.

I looked on the internet but I cannot find the write syntax for the IF statement

I want to have something like:
ALTER VIEW [dbo].[ViewSecurity]
AS
SELECT dbo.tblSecurity.Organisation_TK,
FROM dbo.tblSecurity
WHERE (UserNr = SYSTEM_USER)

IF dbo.tblSecurity.Organisation_TK = '1' THEN
SELECT * from Dual;

ELSEIF dbo.tblSecurity.Organisation_TK = '2' THEN
SELECT * from Dual2;

ELSE
SELECT * from Dual3;

THANK YOU !

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-27 : 07:44:28

You can't put an if statement in a view - it's a control of flow statement

You can also only return a single resultset from a view - you have 2.
maybe something like

select *
from tblSecurity.Organisation_TK a
join Dual d
on a.tblSecurity.Organisation_TK = '1'
where UserNr = SYSTEM_USER
union all
select *
from tblSecurity.Organisation_TK a
join Dual d2
on a.tblSecurity.Organisation_TK = '2'
where UserNr = SYSTEM_USERunion all
select *
from tblSecurity.Organisation_TK a
join Dual d3
on a.tblSecurity.Organisation_TK = '3'
where UserNr = SYSTEM_USER

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JohanDP
Starting Member

2 Posts

Posted - 2011-05-27 : 08:45:58

the problem is that I use views to restricts data.
When In the security table value 1 is set this means a wildcard.

So this means that the user can see all the organisations.
When 2 or 3 is set this mean that the user can only see the organisation of 2 or 3 or 4 or 5, ....

I think this is not possible with a union all of do I see it wrong ?

thank you in advance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-27 : 09:20:45

Should work ok
Would be a lot easier if all your organisations were in the same table with an organisation id.
I take it your Dual tables hold the organisation data

select *
from Dual d1
where exists (select * from tblSecurity where UserNr = SYSTEM_USER and Organisation_TK in ('1'))
union all
select *
from Dual d2
where exists (select * from tblSecurity where UserNr = SYSTEM_USER and Organisation_TK in ('1','2'))
union all
select *
from Dual d3
where exists (select * from tblSecurity where UserNr = SYSTEM_USER and Organisation_TK in ('1','3'))
...

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources