Sql server stored procedure operating summary 01

Syntax:

GRANT CREATE PROCEDURE username /* assign to create stored procedure right to the username user*/

CREATE PROCEDURE procedure_name [;version number]

[{@paramenter data_type}[VARYING][=default value][OUTPUT]][,...n]

[WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FOR REPLICATION]

AS sql_statement[...n]

1.First,create following stored procedure:

Create PROCEDURE usp_Select_Camera @address CHAR(100),
                 @avg_longitude FLOAT OUTPUT,@max_longitude FLOAT OUTPUT,
                 @avg_latitude FLOAT OUTPUT,@max_latitude FLOAT OUTPUT
AS

SELECT * FROM Camera where Area=@address

SELECT @max_longitude=MAX(Longitude) FROM Camera Where Area=@address
SELECT @avg_longitude=AVG(Longitude) FROM Camera Where Area=@address
SELECT @max_latitude=MAX(Latitude) FROM Camera Where Area=@address
SELECT @avg_latitude=AVG(Latitude) FROM Camera Where Area=@address

SELECT '经度最大值'=@max_longitude,'经度平均值'=@avg_longitude
SELECT '纬度最大值'=@max_latitude,'纬度平均值'=@avg_latitude

2.Second step,execute the above stored procedure:

declare @Max_Long FLOAT
declare @Avage_Long FLOAT
declare @Max_Latitude FLOAT
declare @Avage_Latitude FLOAT
EXEC usp_Select_Camera N'黄埔区',@Max_Long OUTPUT,@Avage_Long OUTPUT,@Max_Latitude OUTPUT,@Avage_Latitude OUTPUT
Select 'Max Longitude'=@Max_Long,'Ava Longitude'=@Avage_Long,'Max Latitude'=@Max_Latitude,'Avg Latitude'=@Avage_Latitude

3.Finally,following results: