June 24, 2003 at 12:54 pm
I have a table that contains view names for a specific user. I want to have a procedure that will get the view for a given user, then use it in the from clause of a select statement.
Here’s how I’m doing it now:
---------------------------------------------
Select @ViewName = ViewName From ….. Where user = @userid
Set @sql = 'select …… from ' + @ViewName
Execute (@sql)
Is there a better way???
June 24, 2003 at 1:51 pm
Assunming you have following table structure and the example may the one you are looking for.
USE pubs
GO
CREATE TABLE appointments2
(
patient_id int IDENTITY(2000, 1) NOT NULL,
doctor_id int NOT NULL,
appt_date datetime NOT NULL DEFAULT GETDATE(),
receptionist varchar(30) NOT NULL DEFAULT SYSTEM_USER
)
GO
INSERT appointments2 (doctor_id)
VALUES (151)
INSERT appointments2 (doctor_id, appt_date)
VALUES (293, '5/15/98')
INSERT appointments2 (doctor_id, appt_date)
VALUES (27882, '6/20/98')
INSERT appointments2 (doctor_id)
VALUES (21392)
INSERT appointments2 (doctor_id, appt_date)
VALUES (24283, '11/03/98')
GO
update appointments2 set receptionist = 'install' where doctor_id = 151
GO
create view appointment_view
as
SELECT top 100 percent *
FROM appointments2
where receptionist = SYSTEM_USER
ORDER BY doctor_id
GO
grant select on appointment_view to install
--login as 'sa' and you will see four records
select * from appointment_view
--login as 'install' and you only see one record.
select * from appointment_view
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply