因为空格浪费了我1个小时的时间

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Customer_SCode]
    (
        @SCODE nchar(20)
    )
AS
    SET NOCOUNT ON
    SELECT Customer.NAME AS 客户名称, Customer.ADDRESS AS 地址,
          Customer.LEADER AS 负责人, Customer.DUTY AS 职务, Customer.PHONE AS 电话,
          Customer.MPHONE AS 手机, Customer.EMAIL, Customer.POSTALCODE AS 邮编,
          [User].NAME AS 跟单人, Customer.SCODE AS 缩码, Customer.ID
    FROM Customer INNER JOIN
          [User] ON Customer.USERID = [User].ID
    WHERE (Customer.SCODE LIKE '%'+@SCODE+'%')
    ORDER BY Customer.ADDRESS DESC
    RETURN

/////////////////////////////////////////////////////////////////////////////////////////////////

 

 

USE [EastCustomer]

GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[sp_Customer_SCode]
        @SCODE = N'%K%'

SELECT    'Return Value' = @return_value

GO

测试结果永远都是0行。最后发现因为参数定义为20个字符，在参数传进来后，sqlserver自动正在变量后补空格到20为，所以模糊匹配的时候就找不到了，解决方法只要加“RTRIM（）”就可以了

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Customer_SCode]
    (
        @SCODE nchar(20)
    )
AS
    SET NOCOUNT ON
    SELECT Customer.NAME AS 客户名称, Customer.ADDRESS AS 地址,
          Customer.LEADER AS 负责人, Customer.DUTY AS 职务, Customer.PHONE AS 电话,
          Customer.MPHONE AS 手机, Customer.EMAIL, Customer.POSTALCODE AS 邮编,
          [User].NAME AS 跟单人, Customer.SCODE AS 缩码, Customer.ID
    FROM Customer INNER JOIN
          [User] ON Customer.USERID = [User].ID
    WHERE (Customer.SCODE LIKE '%'+RTRIM(@SCODE)+'%')
    ORDER BY Customer.ADDRESS DESC
    RETURN

Technorati 标签: Edward