[Pierre J. Iskandar]

i want to create a view that takes input from the user and outputs a table accordingly
i.e.: i have a table Customers, a table Movies, and a table Buys; what i want to do is to create a view such that, given a customer's ID it shows his/her purchases from the Buys table referencing the Movies table for the Movie ID

what i tried doing is that i treated it like a procedure, so the code is:

 CREATE VIEW customerpurchases
@cid int not null
AS
SELECT *
FROM Buys
WHERE cid = Buys(CID) 

any help will be extremely appreciated! thanks!

[woodjom]

You cant create a view that accepts inputs.

You want a Store-Procedure (table-valued).

What you could possibly do is create a static view that simply returns ALL field (what i call a View-Join) and then have the Stored Procedure do the conditional return, based on the information passed.

When i do this, my view has all the information i would need to show on the return. In this approach, my views are more for reporting instead of managing data.

But for what you are trying to do this should suffice

Create Procedure usp_WHATEVER
(
  @var INT
)
AS
Select *
FROM Buys
WHERE cid=@var

RETURN

This post has been edited by woodjom: 22 January 2010 - 08:00 AM