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 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

 

[Mike555]

You may want to post future SQL programming questions at...forum183

In your query analyzer code you create the parameter but you don't assign it a value of 'UK' anywhere, which is probably why you aren't seeing results.


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[gus121]

ops wrong forum i was wondering why i was not getting a reply.

i execute the code using the following code which assigns it a value is this not correct?

DECLARE @RC int
DECLARE @ShipLocation varchar(3)
SELECT @ShipLocation = 'UK'
EXEC @RC = [Sailgbdev].[dbo].[usp_ShippingOptionsByAreaGet] @ShipLocation
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: Sailgbdev.dbo.usp_ShippingOptionsByAreaGet'
SELECT @PrnLine = '	Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

-Gus

 

[Mike555]

Maybe try this?

DECLARE @RC int
DECLARE @ShipLocation varchar(3)[b] = 'UK'[/b]
EXEC @RC = [Sailgbdev].[dbo].[usp_ShippingOptionsByAreaGet] @ShipLocation
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: Sailgbdev.dbo.usp_ShippingOptionsByAreaGet'
SELECT @PrnLine = '    Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[Mike555]

Or this also...

DECLARE @RC int
DECLARE @ShipLocation varchar(3)
[B]SET[/B] @ShipLocation = 'UK'
EXEC @RC = [Sailgbdev].[dbo].[usp_ShippingOptionsByAreaGet] @ShipLocation
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: Sailgbdev.dbo.usp_ShippingOptionsByAreaGet'
SELECT @PrnLine = '    Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[gus121]

Hi Mike,

Your a genius your final method works!

This was the SQL Query Analyser generated script when i tryed to 'open' the stored proc. It is strange any ideas why it did not generate the code to Set the @ShipLocation variable?

This will be the key to why my ASP App also does not work with this query.

thanks

Angus



-Gus

 

[mrdenny]

You need to remove the single quotes from around the variable within the proc.

CREATE      PROCEDURE [dbo].[usp_ShippingOptionsByAreaGet]
    @ShipLocation char(3)
AS

SELECT * FROM [Shipping]
WHERE [GeographicalCoverage] = [COLOR=red]@ShipLocation[/color]
ORDER BY [ShippingCode]

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Mike555]

Don't know honestly. You haven't stated why you wanted to 'open' the stored proc in Query Analyzer, but if you were simply doing so to test whether or not the stored proc worked you could have executed it from Query Analyzer using the EXEC command similar to this...

EXEC [usp_ShippingOptionsByAreaGet], @ShipLocation='UK'

--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[gus121]

Hi mike thanks for your help your method works fine i have switched to the SQL programmers forum as it is confussing and wrong to have multiple threads on the same issue.

http://www.tek-tips.com/viewthread.cfm?qid=1072836&page=1

-Gus