SP vs. SELECT Statement

[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
&"'"

 

[Kevin3NF]

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

 

[Jeff via AccessMonster.com]

I tried that but it returns too many records

 

[Sylvain Lafontaine]

What's the value of the incorrect record returned?

 

[Sylvain Lafontaine]

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

 

[Jeff via AccessMonster.com]

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.

 

[Alex White MCDBA MCSE]

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.

 

[Philipp Stiefel]

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.

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

 

[Jeff via AccessMonster.com]

You got it Phil. Thanks,