Create a Store Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create a Store Procedure

Author

Topic

rvan
Starting Member

28 Posts

Posted - 2006-11-22 : 14:39:52

Hello All,

I'm new to this forum and looking for helps tip on SQL Server.
How to create a store procedure? Here's the proper information that below:
1)In house data, have 2 tables called: Advo and
Advosum;included Excel sheet (ChampaignAddress.xls)
have raw data rows
(Latitude1,Latitude2,Longitude1,Store).
2)I have a query statement to retrieve data from
"In House Data" which in Where Clause using
(latitude between # and #)& (longitude). Get each rows data in
column from excel(ChampaignAddress.xls)to populate into Where
Clause that when it execute the query.
3)
SELECT CASE WHEN deliveryTypeCode >= 'A' AND deliveryTypeCode <= 'H' THEN CONVERT(char(20), 'R') ELSE CONVERT(char(20), 'B')
END AS RBDI, case(left(advo.crrt,1))when 'B' then convert(char(20),'BOXHOLDER')
when 'C' then case when (deliveryTypeCode>='A') and (deliveryTypeCode <='H') then convert(char(20),'RESIDENT')
else convert(char(20),'BUSINESS OWNER') end else 'RESIDENT' end as Title, case (left(advo.crrt,1))
when 'B' then convert(char(64),( rtrim(StreetName)+ ' ' + rtrim(streetNum) + ' ' + rtrim(StreetPreDir) + ' ' +
rtrim(StreetPostDir) + ' ' + rtrim(StreetSuffix) + ' ' + rtrim(alternateTopLine) + ' ' +
rtrim(AptNum)))else convert(char(64),(rtrim(streetNum) + ' ' + rtrim(StreetPreDir) + ' ' +
rtrim(StreetName) + ' ' + rtrim(StreetPostDir) + ' ' + rtrim(StreetSuffix) + ' ' +
rtrim(alternateTopLine) + ' ' + rtrim(AptNum))) end as Address, cityName as City, State,advo.ZIP, advo.Plus4,
convert(numeric,ltrim(Walkseq)) as Walkseq, advo.Crrt,('******************ECRWSS**' + advo.Crrt)as Endorse,
cityRuralFlag as City_rural,convert(char(2),replace(dpb,' ','0'))as dpb,dpbc,null as primaryPreName,
null as PrimaryFirstName,null as PrimaryMiddleInitial,null as PrimaryLastName,null as PrimaryPostName,updateDate
FROM advosum squareRadius join Advo On advo.crrt=squareRadius.crrt and advo.zip=squareRadius.zip
WHERE latitude between 40.44294591 and 40.48836091
AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND advo.crrt LIKE 'C%' OR latitude between 40.44294591 and 40.48836091
AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND advo.crrt LIKE 'B%' OR latitude between 40.44294591 and 40.48836091
AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND (advo.crrt LIKE 'R%' OR advo.crrt LIKE 'H%' OR advo.crrt LIKE 'G%')

ORDER BY advo.zip,advo.crrt,walkseq
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Thank you to all for your helps
ryan,

RV

Kristen
Test

22859 Posts

Posted - 2006-11-22 : 16:07:48

OMG!

Please tell us the PROBLEM you are trying to solve rather than your proposed solution.

Are you trying to find the nearest shop to a customer?

"THEN CONVERT(char(20), 'R') ELSE CONVERT(char(20), 'B')
END "

Why-oh-why would you convert to fixed length strings?

"AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3)))) "

Massively computational, heavy on resources, when there are easier ways of achieving this, but that may not be important because you haven't said what the problem actually is - except that you want to convert a Query to a Stored Procedure ...

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33753

Kristen

rvan
Starting Member

28 Posts

Posted - 2006-11-22 : 17:08:42

Hi, Kristen

The problem was received 100 of records business address information in excel sheet that require us to perform a search for single business address (example,Subway Sandwich) within 5 miles radius in residential. In radius would determine how many residentials was close to the center business area. Keypoint here it try to figure out to get excel data (latitude1,latitude2,longitude1) fetch in the query as automatic.

Thank you and happy Thanksgiving Day.
ryan,

RV

Kristen
Test

22859 Posts

Posted - 2006-11-23 : 00:49:49

"Keypoint here it try to figure out to get excel data (latitude1,latitude2,longitude1) fetch in the query as automatic"

You can import the Excel data to a table using DTS.

You can schedule that, and the running of your query too.

What are you going to do with the results of your query?

Kristen

mightypenny_ph
Yak Posting Veteran

54 Posts

Posted - 2006-11-23 : 01:33:22

Are you using SQL Server2000?? If so please look into this link for more details on Data Transformation Services (DTS)... im assuming you're new to DTS.. :)

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx

google search: SQL Server+Data Transformation Services

SlayerS_`BoxeR` + [ReD]NaDa

rvan
Starting Member

28 Posts

Posted - 2006-11-26 : 23:33:45

Hello,Kristen

"Keypoint here it try to figure out to get excel data (latitude1,latitude2,longitude1) fetch in the query as automatic"
Answer to your Question: Yes! Is true to the question above. How would I write this method to fetch in the query?

You can import the Excel data to a table using DTS.
Answer to your Question:Kristen, you recommended to import the
Excel data by using DTS contain (latitude1,latitude2,longitude1)and before able to fetch each fields into query statement "where clause" below>>>>
WHERE latitude between 40.44294591 and 40.48836091
AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND advo.crrt LIKE 'C%' OR latitude between 40.44294591 and 40.48836091 AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND advo.crrt LIKE 'B%' OR latitude between 40.44294591 and 40.48836091 AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND (advo.crrt LIKE 'R%' OR advo.crrt LIKE 'H%' OR advo.crrt LIKE 'G%')
****Before running the query statement to retrieve data from main tables********

You can schedule that, and the running of your query too.

What are you going to do with the results of your query?
Answer to your Question:What would I like to accomplished it to retrieve data out from 2 table "advo" and "advosum".

Thank you
Ryan,

RV

rvan
Starting Member

28 Posts

Posted - 2006-11-26 : 23:45:57

Hi, MightyPenny

Thank you for show me the link to DTS information. I'm still has a hold lot to learn about sql server.

ryan,

quote:
Originally posted by mightypenny_ph

Are you using SQL Server2000?? If so please look into this link for more details on Data Transformation Services (DTS)... im assuming you're new to DTS.. :)

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx

google search: SQL Server+Data Transformation Services

SlayerS_`BoxeR` + [ReD]NaDa

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-27 : 13:04:55

I think you are confusing 2 things

First you want to read this

http://en.wikipedia.org/wiki/Great-circle_distance

Second, you probably want to create a UDF

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources