Querying with State Codes... - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Querying with State Codes...

Author

Topic

PETE314
Starting Member

37 Posts

Posted - 2006-10-10 : 12:56:40

ok I have the folowing stored procedure...

Alter Procedure procCountyCombobox
(
@ClientID nvarchar(6),
@State nvarchar(2)
)
/*Retrieves the next 25 rows based on OrderInfoID. If no OrderInfoID supplied then starts at the lowest OrderInfoID.
*/
WITH RECOMPILE
AS
SET NOCOUNT ON
-- Variables used for getting County List
SELECT tblClientCounties.strCountyCode, tblCounties.strCounty
FROM (tblClientCounties INNER JOIN tblPropertyDetails ON [tblClientCounties].[strClientID] = [tblPropertyDetails].[strClientID]) INNER JOIN tblCounties ON [tblClientCounties].[strCountyCode] = [tblCounties].[strCountyCode]
WHERE (((tblClientCounties.strClientID) = @ClientID ) AND ((tblCounties.strStatePostalCode) = @State ))
GROUP BY tblClientCounties.strCountyCode, tblCounties.strCounty, tblClientCounties.strClientID
HAVING (((tblClientCounties.strClientID) = @ClientID ))

Everything works great except when I have to pull up records for the state of Indiana whose state code is IN. It works fine when manually entering the parameters whether thru the query analyzer or opening the stored proc directly in the Access Project. As I am using an Access Project front end, the procedure is being used to fill the source of a combo box. I programatically have the rowsource param set when the form opens...so it would end up looking like such

exec procCountyCombobox LENTRE, MI

The problem is when it tries to execute while the second parameter is IN

I believe that IN is a keyword....

A quick and ugly solution is to Change Indiana's state code to IX or something else, but that really isn't a real solution for us.....

I figure there is something that can be done either in how I am calling the procedure or something in the procedure itself. Any ideas would be helpful

Thanks...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-10 : 13:11:24

The problem is that you are not surrounding your strings with quotes:


exec procCountyCombobox 'LENTRE', 'MI'

The (other (problem (is that) you (((have)) way)) (((too) many) parenthesis) (in ((your))) code.) (What crappy tool did you use to generate it?( ((Access)?)

"Once you establish possibility, all that remains are desire and determination."

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-10 : 13:38:12

quote:
Originally posted by blindman

The problem is that you are not surrounding your strings with quotes:

exec procCountyCombobox 'LENTRE', 'MI'

The (other (problem (is that) you (((have)) way)) (((too) many) parenthesis) (in ((your))) code.) (What crappy tool did you use to generate it?( ((Access)?)

"Once you establish possibility, all that remains are desire and determination."

OK, I agree with the blind dude (even though this is coming from a guy who uses full table names to qualify columns, as you are doing, simplify the query)....

Also, don't do Alter Procs, drop and recreate them

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 14:21:08

And why do you have both WHERE and HAVING for the same criteria?
Also, why are you GROUPing for more columns that you display?

Try this instead

SELECT DISTINCT	tblClientCounties.strCountyCode,
		tblCounties.strCounty
FROM		tblClientCounties
INNER JOIN	tblPropertyDetails ON [tblPropertyDetails].[strClientID] = [tblClientCounties].[strClientID]
INNER JOIN	tblCounties ON [tblCounties].[strCountyCode] = [tblClientCounties].[strCountyCode]
WHERE		tblClientCounties.strClientID = @ClientID
		AND tblCounties.strStatePostalCode = @State

Peter Larsson
Helsingborg, Sweden

PETE314
Starting Member

37 Posts

Posted - 2006-10-10 : 15:29:04

thanks for the info...it is much appreciated... In this case it was a quick query made...I was eventually going back to simplify...but your suggestions are a large help....Actually I can't believe I missed the quotes and as soon as I saw it I had a "DOH!!" moment...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-10 : 15:39:17

quote:
Originally posted by X002548

Also, don't do Alter Procs, drop and recreate them

Why? We're continuing the conversation over here.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources