jeudi 9 juillet 2015

stored procedure with a for loop

I have two tables: one that is called City (City_id,Name, Latitude, Longitude) and the other is Measures (City_id, Measure_Date, Temperature, Humidity, WindSpeed, AirPressure).

City_id is the primary key in City and foreign key in Measures. City_id and Measure_date are primary key too.

I am trying to write a stored procedure in SQL Server 2005 that allows me to calculate the averages of the all four measurements I have in the database for a specific city. And if the city has no records in Measures, I want the default value to be null.

How should I make it set the default value null for those cities? Thank you :)

Here is the code that I did:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[low_temp]
     @temp real
AS
begin
    select 
        avg(m.temperature), avg(m.humidity), 
        avg(m.windspeed), avg(m.airpressure)
    from 
        city c, measures m 
    where 
        m.city_id = @temp
end

Aucun commentaire:

Enregistrer un commentaire