[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

When running a stored procedure with a parameter, why does 4T give me the same result as 4?

Posted on 2005-04-21
4
Medium Priority
?
206 Views
Last Modified: 2011-04-14
My stored proc worked great when CarNumber was an integer, but I found out that the data may be more than just a number.  It could include letters too!  

Here is the stored proc:

CREATE PROCEDURE dbo.GetDriverInfoSP
@CarNumber as varchar
AS
SELECT
CarNumber,
LastName=rtrim(LastName),
FirstName=rtrim(FirstName),
Equipment,
Engine
FROM
Qualifications
WHERE CarNumber = @CarNumber
GO

CarNumber    LastName     FirstName    Equiptment   Engine
4                | Madison     | Davey       | Prototype 1 | Chevy
4T              | Clark          | Mike         | Prototype 2 | BMW

Datatypes

CarNumber = varchar
LastName = varchar
FirstName  = varchar  
Equiptment = varchar  
Engine = varchar

The following Line in Query Analyser:

GetDriverInfoSP '4T'

gives me the same results that the following line gives me:

GetDriverInfoSP '4'

What gives?
0
Comment
Question by:indy500fan
  • 3
4 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13838628
Change your parameter definition from

@CarNumber as varchar

to

@CarNumber as varchar(10)

By default, its length is 1.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13838632
This is why you get the same result for '4T' and '4'.  Your query is just comparing it against '4'.
0
 

Author Comment

by:indy500fan
ID: 13838658
Yep!  That worked.  I wish I had thought of that!!!  Thanks for the extra fast response time!!!
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13838678
You're welcome.  Glad I was able to help.  Have a good day.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question