Stored Procedure Problem - 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

SQL Server Development (2000)

Stored Procedure Problem

Author

Topic

tanderton
Starting Member

2 Posts

Posted - 2003-02-07 : 10:56:38

I'm having a problem with a simple query:

CREATE PROCEDURE sp_GetHMSBySerialTest
(
@serial char(120)
)
AS

SELECT
*
FROM
HMS
WHERE Serial LIKE '%' + @serial + '%'

GO

I know there is plenty of data that matches, but it always returns 0. I think it's related to the data type. Any ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-07 : 11:01:11

Try declaring the variable as varchar instead of char:

CREATE PROCEDURE sp_GetHMSBySerialTest ( @serial varchar(120) ) AS
SELECT * FROM HMS
WHERE Serial LIKE '%' + @serial + '%'

Casting it as char data will pad it out with spaces to make up the length (120 characters), varchar will not pad.

tanderton
Starting Member

2 Posts

Posted - 2003-02-07 : 11:11:20

Worked like a champ! Thanks for the quick response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources