PRSQL9103033: Extra Space Is Added to Local Variables

ID Number: Q70853

1.10

OS/2

buglist1.10 fixlist1.11

Summary:

PROBLEM ID: PRSQL9103033

SYMPTOMS

A stored procedure returns different results from a straight SQL

statement when using "LIKE" in the WHERE clause.

CAUSE

Local variables in stored procedures are implemented with an extra

space added to the end of the variable. For example, "%" becomes

"% ".

WORKAROUND

Either of the following options will resolve the problem:

1. In the WHERE clause, convert the column type to varchar. For

example:

SELECT type FROM titles WHERE convert(varchar,type) LIKE @var

2. Use the RTRIM string function to remove the trailing blanks.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version

1.1. This problem has been corrected in SQL Server version 1.11,

which is available from Microsoft Product Support Services. For

more information, contact your primary support provider.