About Stored Procedure

hi there,

I am using SQL after a long time and want to use stored procedure ofor the following purpose:

i have to db of all the names of agents all over US.i am using a formulata to calculate zip to zip distance so that when the user gets a order and hits"find agents" ..it calculates the distances by using latitude and longitude calculation and displays all the agents who are around 50 mile in radius with tthe zip code specified in the order.

I wanted to know if I could store this formula in a stored procedure so that all teh calculations are done by SQL and stored in a temp table..beucase the same thing i did in Access(obviously access doesnt have Stored proicedure and i wrote a program called calculate.asp to calculate diostances) its running slow and thats the only reason i am using SQL.

i dont know where to start from...any help/links/ideas on how to do it ,whether its possible to do it thru stored procedures...if so then how to write stored procedures?

hi,

yes, you can put that "solving" code in a stored procedure...

for instance, assuming you have "areas" indicated in an x,y cartesian table of 3x3 cells as the following trivial sample, you can get the related agent based on the join of the area and agents tables...

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.Area (

Id int NOT NULL IDENTITY PRIMARY KEY,

Name varchar(10) NOT NULL,

X int NOT NULL,

Y int NOT NULL

);

CREATE TABLE dbo.Agent (

Id int NOT NULL IDENTITY PRIMARY KEY,

Name varchar(10) NOT NULL,

IdArea int NOT NULL

);

INSERT INTO dbo.Area VALUES ( 'a', 1 , 1 );

INSERT INTO dbo.Area VALUES ( 'b', 2 , 1 );

INSERT INTO dbo.Area VALUES ( 'c', 3 , 1 );

INSERT INTO dbo.Area VALUES ( 'd', 1 , 2 );

INSERT INTO dbo.Area VALUES ( 'e', 2 , 2 );

INSERT INTO dbo.Area VALUES ( 'f', 3 , 2 );

INSERT INTO dbo.Area VALUES ( 'g', 1 , 3 );

INSERT INTO dbo.Area VALUES ( 'h', 2 , 3 );

INSERT INTO dbo.Area VALUES ( 'i', 3 , 3 );

INSERT INTO dbo.Agent VALUES ( 'agent a', 1 );

INSERT INTO dbo.Agent VALUES ( 'agent b', 2 );

INSERT INTO dbo.Agent VALUES ( 'agent c', 3 );

INSERT INTO dbo.Agent VALUES ( 'agent d', 4 );

INSERT INTO dbo.Agent VALUES ( 'agent e', 5 );

INSERT INTO dbo.Agent VALUES ( 'agent f', 6 );

INSERT INTO dbo.Agent VALUES ( 'agent g', 7 );

INSERT INTO dbo.Agent VALUES ( 'agent h', 8 );

INSERT INTO dbo.Agent VALUES ( 'agent i', 9 );

DECLARE @.x int, @.y int;

SELECT @.x = 3, @.y = 1

SELECT a.Name AS [AreaName], g.Name AS [AgentName]

FROM dbo.Area a

JOIN dbo.Agent g

ON g.IdArea = a.Id

WHERE a.X = @.x AND a.Y = @.y

PRINT 'all agents and related areas';

SELECT a.Name AS [AreaName], g.Name AS [AgentName]

FROM dbo.Area a

JOIN dbo.Agent g

ON g.IdArea = a.Id;

DROP TABLE dbo.Agent, dbo.Area;

--<

AreaName AgentName

- -

c agent c

all agents and related areas

AreaName AgentName

- -

a agent a

b agent b

c agent c

d agent d

e agent e

f agent f

g agent g

h agent h

i agent i

the procedure related code could be as easy as the following related trivial code

CREATE PROCEDURE dbo.GetAgentByArea (

@.x int,

@.y int

)

AS BEGIN

SELECT a.Name AS [AreaName], g.Name AS [AgentName]

FROM dbo.Area a

JOIN dbo.Agent g

ON g.IdArea = a.Id

WHERE a.X = @.x AND a.Y = @.y;

END;
GO

EXEC dbo.GetAgentByArea 1,1;

EXEC dbo.GetAgentByArea 3,2;

GO

DROP PROCEDURE dbo.GetAgentByArea

or as complicated as necessary..

regards