Need Expert SQL SP help

[sqlchallenged]

Hi everyone.. Im a ASP programmer in need of help. I have a sql query which i can seem to input into a stored procedure.

I have two tables.... zip and dealers heres the code

SQL Server vesion - use ACOS()**
sSql = "SELECT name, phone, addr1, city, state, zip " & _
"FROM dealer " & _
"WHERE zip in ( SELECT ZIP_CODE FROM zip "& _
"WHERE @radius > 3959 * ACOS(SIN(@lat/ 57.3) * SIN(LAT/ 57.3) + " & _ "COS(@lat/ 57.3) * COS(LAT/ 57.3) * " & _ "COS((LNG/ 57.3) - (@lng/ 57.3))) ) " & _
ORDER BY name

I have the access query but cant seem to get it..

Please anyhelp will be great

 

[earthandfire]

I haven't tested anything, just had a quick look, but I don't think that you need the second WHERE.

Also there may be an ambiguous reference where you have 'zip' as field and as a table as in the first part of the WHERE clause.

 

[JamesLean]

Try this (it is syntactically valid at least):

CREATE PROC myproc
	@radius int,
	@lat decimal(10, 2),
	@lng decimal(10, 2)
AS

SELECT name, phone, addr1, city, state, zip
FROM dealer
WHERE zip IN (
		SELECT ZIP_CODE
		FROM zip
		WHERE @radius > 3959 * ACOS(SIN(@lat / 57.3) * SIN(LAT / 57.3) + COS(@lat / 57.3) * COS(LAT / 57.3) * COS((LNG / 57.3) - (@lng / 57.3)))
	)
ORDER BY name

--James

 

[sqlchallenged]

thanks alot for the help a little tunning on my asp code and it works great..

Thnks