Very Basic stored Proc does not work?

[gus121]

I have created a very basic stored procedure which displays Shipping Options based on geographic location.
The unique identifier is a three character field called 'GeographicalCoverage'

For example bellow works fine and retrieves all records from the UK as i would expect:

SELECT * FROM [Shipping]
WHERE [GeographicalCoverage] = 'UK'
ORDER BY [ShippingCode]

However the stored proc bellow retrieves no records in query analyser. And i cant understand why when i open the stored proc and declare @ShipLocation = UK any ideas?

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE      PROCEDURE [dbo].[usp_ShippingOptionsByAreaGet]
    @ShipLocation char(3)
AS

SELECT * FROM [Shipping]
WHERE [GeographicalCoverage] = '@ShipLocation'
ORDER BY [ShippingCode]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

thanks

-Gus

 

[vongrunt]

This is SQL-related question (ASP = zip, nope, Empty) but anyway... remove single quotes surrounding @ShipLocation.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gus121]

Hi thanks for your reply.
Removing the quotes makes no difference.

I got it to work in the ASP but am still confused why the stored procs not working.

here is the ASP.

	dim Sql
	sql = "SELECT * FROM Shipping WHERE GeographicalCoverage = '"& ShipLocation &"' ORDER BY ShippingCode"
	rs.Open Sql, gConn
	response.write("<h1>" & rs.Fields("GeographicalDescription").value & "</h1>")  
	do while NOT rs.EOF

any ideas most appreciated.

thanks




-Gus