Create 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)

Create View

Author

Topic

sqlmyworld
Starting Member

38 Posts

Posted - 2013-10-07 : 00:07:54

Requirement - A view is executed by an application. we have a requirement that if there is a specific login/user (let's say "TestUser") then view should select data from different db which resides on different server and refer using linked server and for rest of users data should select from local db.

I am not sure if following code is possible? if not, is there any other way I can create a view which satisfy our requirement? Thanks in Adv.
--------------------------------------------
Create View V1
AS
Select t.col1, t.col2
FROM
IF SUSER_NAME() = 'TestUser" -- only for TestUser
Linkedserver.DB2.dbo.Table1 t
ELSE
DB1.dbo.Table1 t-- rest of the users select data from local db
--------------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 05:59:45

you cant use IF clause like that it should be


Create View V1
AS
Select t.col1, t.col2 
FROM  -- only for TestUser
Linkedserver.DB2.dbo.Table1 t
WHERE SUSER_NAME() = 'TestUser'
UNION ALL
Select t.col1, t.col2 
FROM DB1.dbo.Table1 t-- rest 
WHERE SUSER_NAME() <> 'TestUser'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sqlmyworld
Starting Member

38 Posts

Posted - 2013-10-08 : 00:06:34

Thanks Visakh.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources