1 Replies - 982 Views - Last Post: 22 January 2010 - 07:59 AM

#1 Pierre J. Iskandar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 19-October 09

Creating a view in MSSQL server 2005

Posted 22 January 2010 - 07:08 AM

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!

Is This A Good Question/Topic? 0
  • +

Replies To: Creating a view in MSSQL server 2005

#2 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: Creating a view in MSSQL server 2005

Posted 22 January 2010 - 07:59 AM

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

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1