Can I reference a table in a view from a stored procedure?

Question

I've created a view on 3 tables in my database, they are as follows:

Activity

ActivityRole

aspnet_UsersInRoles

I am trying to grab the combination of what's in Activity and ActivityRole, if the UserId I pass in is a member of the role specified as required in the Activity Role.

I'm creating a view, because I want to generate an object off of the view that is called Activity that has fields from Activity and ActivityRole.

My first attempt was to create the view that INNER JOINED Activity to ActivityRole and ActivityRole to the UserRoles on Activity.ActivityId = ActivityRole.ActivityId and ActivityRole.RoleId = aspnet_UsersInRoles.RoleId

Now I want to only pull back records by a UserId located in the aspnet_User_UserRoles table.

My inclination is to write a stored procedure which does:

SELECT * From MyView WHERE aspnet_UsersInRoles.UserID = @UserID

However I cannot reference the aspnet_UsersInRoles table in the view via the stored procedure.

Is my approach totally wrong?

Thank you for any assistance.

Answer 1

You should look at a view as you look at a table, something with rows and columns. From the outside you see just the columns you defined in the view, not the underlying columns from the tables you created the view from. So in your case, MyView does not have a column named aspnet_UsersInRoles.UserId.

To be able to query on this column, you should include it in your view. Then you can do something like:

SELECT * FROM MyView v WHERE v.UserId = @UserId