@password执行错误

Question

需要您对此代码的建议...我有客户的表格登录信息。我正在使用SQL Server。一切正常，除了当我输入apphabet与数字作为@password值时，我一直得到错误，而只有数字没有错误。请帮我找出错误！

    CREATE PROCEDURE [dbo].[usp_login] 
    @customer_id numeric(5,0) = -1,     
    @password nchar(20) = '',           
    @customer_level numeric(2,0) = -1   
AS

BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL AS varchar(300)
    DECLARE @WHERE1 AS varchar(200) = ''

    SET @SQL = 'SELECT *FROM Login'

    IF (@customer_id != -1)
    BEGIN
        SET @WHERE1 = @WHERE1 + 'id=' +  CONVERT(varchar,@customer_id)
    END

    IF (@password != '')
    BEGIN
        IF (@WHERE1 != '')
            SET @WHERE1 = @WHERE1 + ' and ' 
        SET @WHERE1 = @WHERE1 + 'password=' + @password
    END

    IF (@customer_level != -1)
    BEGIN
        IF (@WHERE1 != '')
            SET @WHERE1 = @WHERE1 + ' and ' 
        SET @WHERE1 = @WHERE1 + 'customer_level=' +  CONVERT(varchar,@customer_level)
    END


    IF (@WHERE1 != '')
        SET @SQL = @SQL + ' where ' + @WHERE1

    EXEC(@SQL)
    RETURN @@ROWCOUNT

END

Answer 1

由于执行的是动态sql，因此需要用单引号将字符串类型括起来。

--Problems starts here
SET @WHERE1 = @WHERE1 + 'password=' + @password

--You can correct it as below
SET @WHERE1 = @WHERE1 + 'password=''' + @password + ''''