SQL Server Performance Forum – Threads Archive

Stored Procedure using Views

I have two users in database,
BOM
DEL I create table dbo.User_View with following data
NameLoc
Bombay BOM
Delhi DEL
Tirupur TIR
Delhi 1 DEL
Delhi 2 DEL
Bombay 1 BOM I create following three views
CREATE VIEW dbo.UserView
AS
SELECT dbo.User_View.*
FROM dbo.User_View CREATE VIEW DEL.UserView
AS
SELECT dbo.User_View.*
FROM dbo.User_View
WHERE (Loc = ‘DEL’) CREATE VIEW BOM.UserView
AS
SELECT dbo.User_View.*
FROM dbo.User_View
WHERE (Loc = ‘BOM’) I want to create a stored proc sp_userview
create proc sp_userview
as
select * from Userview I want that User should see result based on his login. i.e. if Login is BOM, he should see only 2 rows from table.
Bombay BOM
Bombay 1 BOM Is it possible? If yes, whats the procedure? Thx in advance
Harsh

---

You might have a play with USER_NAME(), eg.
CREATE VIEW dbo.UserView
AS
SELECT blahblah
FROM blahblah
WHERE (Loc = USER_NAME())
But from your code something like
CREATE PROCEDURE dbo.userview
AS
SELECT…FROM… WHERE (Loc=USER_NAME())
should be enough. Btw, you shouldn’t prefix your sp with sp_. SQL Server will assume this is a system procedure (that’s what sp stands for) and _always_ look up in master first. Try something like usp_ or anything else you like.
–Frank
http://www.insidesql.de

---

Coooool
Thx Frank…..its more creative solution.
btw, i normally use dbp as prefix….thx for suggestion. Thx
Harsh Harsh

---

]]>