Views - how to get the results

[micang]

SQL 2000

Hi All,

I have created a view as follows:

Create View Cars
as
select *
from vehicles 
where vehicle_type = '1'

Then I run:

select *
from cars

My question is, that if the vehicle type in the original vehicle_type table is amended or changed etc, then when I run the second code, the changes are not reflected. How do get it to 'refresh'?

If I drop and re-create the view then the changes are there, but this seems pointless as why have a view in the first place?

Many thanks.

Michael

 

[TheJFM]

Hi

What do you mean by the vehicle_type table? Does the vehicles table reference it?

** Don't reach for the moon when you have the stars. ;-) TheJFM **

 

[micang]

I have one table called vehicles.

In this table I have several columns, one of them is vehicle_types (data in the table is 1, 2,3 etc)

Hope this helps.

Michael

 

[SQLSister]

Views are not useful when you want to change the parameters. Use a stored procedure instead. And BTW select * is a poor way to go, never choose any more columns than you need to.

What views are useful for is to pull a subset of data (particulalry when you have complex joins) say by client or year. Then you add the limiting parameters inthe sql statment where you call the view. This is one way of securing the data so that client's can't see other clients data for instance bcause they only have right s to the view and not the whole table.

Questions about posting. See faq183-874

 

[micang]

Thanks SQLSister.

How would I go about converting the above to a stored procedure so that everytime I run the sp I get the updated values?

I gave it a try, but I am not getting it to work.

Many thanks

Michael

 

[micang]

I think I got it to work:

Create procedure sp_car
as
select vehicle_type, id_key
from vehicles
where vehicle_type = '1'

Then when I call the procedure using

exec sp_car

I should get the latest information from the table?

Thanks

Michael

 

[TheJFM]

What the OP is referring to is not a parameterised query. It is a simple view over a table whose content is changing. I agree the select * is not good syntax but not the root of the problem.

There is no reason at all why a query on the view should not reflect changes to the underlying data. As Michael says that is the point of a view.

Michael. Please step through exactly what you are doing.

Here's what I did.

CREATE TABLE [vehicles](
[VehicleName] [varchar](50) null,
[VehicleType] [int] NULL
) ON [PRIMARY]

Values in the table:

Ford 1
Citroen 2
Audi 3


CREATE VIEW [Cars]
AS
SELECT VehicleName, VehicleType
FROM Vehicles
WHERE (VehicleType = 1)

select * from cars

gives me Ford 1

Change table values to

Ford 4
Citroen 2
Audi 1

select * from cars

gives me Audi 1











** Don't reach for the moon when you have the stars. ;-) TheJFM **

 

[TheJFM]

Michael,

The choice between sp and view depends on how you plan to use the info. Either is valid depending on circumstances

** Don't reach for the moon when you have the stars. ;-) TheJFM **

 

[micang]

Hi JFM,

Many thanks for the help, that does indeed work.

In my case, I know that the data will be changing pretty frequently. I think I can get away with using the view.

But, let's say I need to change the parameters in the 'where' clause? Does it differ in this case either using a view or a procedure?

Many thanks

Michael

 

[TheJFM]

Hi

I would use a view if I know the parameter to be passed will not change even if the underlying date will change frequently.

i.e. if you always want the result to reflect vehicle_type of 1

If, however, the parameter will change then I would use an sp.

i.e. if I want to know the results for a number of vehicle types (say one day type 1 and another day type 3) or the vehicle type meaning might change



** Don't reach for the moon when you have the stars. ;-) TheJFM **

 

[gmmastros]

You can also apply a where clause to a view.

Ex:

Create View Cars
as
select *
from vehicles 
[s]where vehicle_type = '1' [/s]

Select * From Cars Where Vehicle_Type = '1'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[micang]

Thanks for the info JFM and George.

I didn't even thin of that george, makes so much sense, ta.

Michael