Alter view from SP

[UrlOnz]

The problem is there are 40 users most of whom are not allowed to see records assigned to others. Theses users need reports. The reports are generated from an Access front end. The reports are altered when the underlying view is altered. When the record number is changed in the view the report is then based on that record. It is more complicated than just that but that covers the reason for changing the view. Trust me the report has to be done from an existing view then the criteria of the views WHERE statement has to be changed so that a new record is selected from the three tables in the query. My users could not write a view on a bet. They need to enter a number on a form and get their report. That is it.
The problem is that the ALTER VIEW statement causes an error at the key word VIEW. I suspect that is because there are nested AS statements. I want to alter the view in side a stored procedure so that I don’t have to alter the view from code on the client side. I am doing that now but I would like to have the code on the server side.

 

[MinusM]

Try execute statement

like

declare @Strsql varchar(3000)
SET @strsql =' Alter VIEW view1 as SELECT * FROM MyTable WHERE Mycondition'

EXECUTE(@strsql)


But you have to take care of multiple users changeing the view at the same time. Is it what you want?

Best of luck