Stored Procedure not returning values

Dear Experts,
I haven't written a stored procedure for almost a year and thought I knew what I was doing but I must have forgotten something! I have looked at the statements over and over and can not for the life of me figure out why it's not working. It's a very simple table. 3 columns, StateLongName, StateAbbreviation, and CountryAbbreviation.

The procedure is to search for all the States or Provinces that are related to a particular country. If I Exec the stored procedure with the parameter given as US, nothing is found. If I comment out the parameter and assign the filter with N'US' it finds the 50 states. What have I forgotten? What am I missing? The code is below:

Database type: MSSQL 2012

This works:
ALTER PROCEDURE [dbo].[usp_Select_States_By_Country_by_RB]
/*(
	@Country nchar
)*/
AS
BEGIN
SELECT        
	StateLongName, 
	StateAbbreviation, 
	CountryAbbreviation
FROM  dbo.StateProvince
WHERE (CountryAbbreviation = N'US')
ORDER BY StateLongName		
END

Open in new window


This doesn't work:
ALTER PROCEDURE [dbo].[usp_Select_States_By_Country_by_RB]
(
      @Country nchar
)
AS
BEGIN
SELECT        
      StateLongName,
      StateAbbreviation,
      CountryAbbreviation
FROM  dbo.StateProvince
WHERE (CountryAbbreviation = @Country)
ORDER BY StateLongName            
END

Thanks for your help.
bobbellowsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
NCHAR if a fixed width. I would use varchar or nvarchar (both will work). Give it the width of your table column, too:
ALTER PROCEDURE [dbo].[usp_Select_States_By_Country_by_RB]
(
      @Country nvarchar(3) -- or (5), or (10), whatever is your table column width.
)
AS
SET NOCOUNT ON
SELECT
      StateLongName,
      StateAbbreviation,
      CountryAbbreviation
FROM  dbo.StateProvince
WHERE (CountryAbbreviation = @Country)
ORDER BY StateLongName

Open in new window

I also put a SET NOCOUNT ON in there, because some report writers that can inject status messages in your return.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantCommented:
also give a length to your parameter:
 @Country nchar(YourFieldLengthHere)

Open in new window

bobbellowsAuthor Commented:
dsacker,
It's nchar in the db. Do I have to change it there too? Would it be better to change it there?
Bob Bellows
Éric MoreauSenior .Net ConsultantCommented:
but you surely have a length in your field declaration. Be sure to use the same length in the stored procedures.

the reason is that when you have "char" fields, the blanks trailing the values are important in the comparison (where it is not with varchar)
bobbellowsAuthor Commented:
Mr. Moreau,
Thanks. It's always good to know why to do something so I don't make the same mistake again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.