Microsoft KB Archive/70853

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:

  In the WHERE clause, convert the column type to varchar. For example: SELECT type FROM titles WHERE convert(varchar,type) LIKE @var   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.