SP vs. SELECT Statement

  • Thread starter Thread starter Jeff via AccessMonster.com
  • Start date Start date
J

Jeff via AccessMonster.com

I have a couple of SELECT statements in my frontend that I'm trying to
convert over to SP but I'm having some problems with my nvarchar data type.
Here's my SP:

PROCEDURE SP_SearchLastName
@GetLastName nvarchar
AS
SELECT * FROM Cases
WHERE [Last Name] LIKE @GetLastName

When I run this code with the name "Stewart" I get one record returned that
is not correct.

However, when I run the SELECT statement below on the frontend I get 6
correct records returned:

"SELECT * FROM dbo.Cases WHERE dbo.Cases.[Last Name] LIKE '" & StrLastName
&"'"
 
CREATE PROCEDURE SP_SearchLastName
@GetLastName nvarchar
AS
SELECT * FROM Cases
WHERE [Last Name] LIKE @GetLastName + '%'

or

CREATE PROCEDURE SP_SearchLastName
@GetLastName nvarchar
AS
SELECT * FROM Cases
WHERE [Last Name] LIKE '%' + @GetLastName + '%'



--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

www.experts-exchange.com - experts compete for points to answer your
questions
 
Also, don't use the SP_ or the XP_ prefixes; they have a special meaning
under SQL-Server and should be reserved to system stored procedures. Their
improper use can lead to some strange bug.

And for you first question, I don't see why you are using the predicate LIKE
instead of the equality operator with a value such as 'Stewart' (ie. a value
without any '%').
 
None of the suggestions worked. The SP returns one record with last name
"s" (don't ask we why there's a last name with just one letter). In any
case, that's what's returned.
 
Is the Stored proc only being used by the form you are currently calling it
from?

because if it is, the call the parameter @StrLastName instead of
@GetLastName and when you are calling the stored proc don't use the
parameter, it should be passed automatically from the form because it is the
same name as a control on the form.

also try to hardcode your select statement in the stored procedure just for
testing e.g. without the parameter.
 
Jeff via AccessMonster.com said:
I have a couple of SELECT statements in my frontend that I'm trying to
convert over to SP but I'm having some problems with my nvarchar data type.
Here's my SP:

PROCEDURE SP_SearchLastName
@GetLastName nvarchar
AS
SELECT * FROM Cases
WHERE [Last Name] LIKE @GetLastName

When I run this code with the name "Stewart" I get one record returned that
is not correct.
Click to expand...

You defined @GetLastName as NVarchar but you haven't specified
the length of that parameter. - So it defaults to length 1.
No matter what value you do supply it gets truncated to the first
character.

Change it to:

PROCEDURE SP_SearchLastName
@GetLastName nvarchar(50)
AS
....

The length of the parameter should be same as the length of
[Last Name] in the table definition.

cheers
Phil
 
Back
Top