Problem with basic stored proc1!? 1

[gus121]

Finaly got the right forum

I have created a very basic stored procedure which displays Shipping Options based on geographic location.
The unique identifier is a three character letter 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?

CODE
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

-Gus

 

[jbenson001]

Since you are using Char(3) it may be trying to find 'UK ' instead of 'UK'. You can use the RTRIM() function or change your parameter to varchar(3)

 

[gus121]

No tryed changing to VarChar previous same result. ie.. empty recordset

thanks

-Gus

 

[jbenson001]

Remove the quotes around your variable:

WHERE [GeographicalCoverage] = '@ShipLocation'

change to:
WHERE [GeographicalCoverage] = @ShipLocation

 

[mrdenny]

See the thread in the Windows forum. Remove the single quotes around the variable.

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

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

By putting the variable in single quotes you are looking for column "GeographicalCoverage" = the value "@ShipLocation" not the value of the variable @ShipLocation.

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

 

[gus121]

Thanks it makes no difference @shipLocation is letters in any case and therefore should be in single quotes. Am i wrong?

-Gus

 

[jbenson001]

Yes, read mrdenny's post. You need to remove the quotes.

 

[gus121]

i have done makes no diffence.

-Gus

 

[vongrunt]

Can you Response.Write code used to call stored procedure?

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

Sure here it is.

	With cmd                                                            
		.CommandText = "usp_ShippingOptionsByAreaGet"                                
		.CommandType = adCmdStoredProc                                  
		.ActiveConnection = gConn   
		.Parameters.Append .CreateParameter("@ShipLocation", adChar, adParamInput, 3)
		.Parameters("@ShipLocation")		= CStr(ShipLocation)                                           
	End With                                                            
	                           
	rs.Open cmd

-Gus

 

[vongrunt]

To narrow possible sources of error, if you do:

exec usp_ShippingOptionsByAreaGet 'UK'

... in Query Analyzer, does everything work OK? (Without single quotes of course).

------
"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 vongrunt that method works fine in SQL query analyser.
I still not much closer to understanding the reason behined my method not working and the reason why if i 'open' in sql query analyser the script is wrong is it to do with data types i guess.

thanks

-Gus

 

[vongrunt]

Ok then... you can also debug value for ShipLocation variable. That value should be UK without any other character, right?

------
"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 Jack. i guess the issue must have been the single quotes its just i tryed so many possibilities i must have missed what i have and what i had not tried.

Incidently If i 'open' the stored proc in sql query analyser the sql script generted is for some reason wrong with this stored proc this is a bit strage (this is the usual way i test my stored procs) perhaps this is not the most reliable way to test a stored proc? i will use the exec method in the future.

thanks for your help angus

-Gus

 

[gus121]

i mean vongrunt not jack sorry.

-Gus

 

[mrdenny]

Not to be a pain in the @ss but take a look at the 5th message in the thread.

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