将 varchar 值转换为数据类型为 int 的列时发生语法错误

[truesight]

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'xjtu_search'
AND type = 'P')
DROP PROCEDURE xjtu_search
GO

CREATE PROCEDURE xjtu_search
@field varchar(255),
@keyword varchar(255),
@categoryid int = 0
AS
DECLARE @STR varchar(1024)
IF (@categoryid=0)
SET @STR='SELECT ID,BookName,Writer,Publisher FROM BookStore WHERE '+@field+' LIKE ''%'+@keyword+'%'''
ELSE
SET @STR='SELECT ID,BookName,Writer,Publisher FROM BookStore WHERE CategoryID='+@categoryid+' AND '+@field+' LIKE ''%'+@keyword+'%'''
EXECUTE(@STR)
GO

不指定CategoryID值的时候可以运行，而且正常，就是说if的那部分没问题；
可是指定CategoryID的值，就出现“将 varchar 值 'SELECT ID,BookName,Writer,Publisher FROM BookStore WHERE CategoryID=' 转换为数据类型为 int 的列时发生语法错误。”错误

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

什么问题？

[txlicenhe]

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'xjtu_search'
AND type = 'P')
DROP PROCEDURE xjtu_search
GO

CREATE PROCEDURE xjtu_search
@field varchar(255),
@keyword varchar(255),
@categoryid int = 0
AS
DECLARE @STR varchar(1024)
IF (@categoryid=0)
SET @STR='SELECT ID,BookName,Writer,Publisher FROM BookStore WHERE '+@field+' LIKE ''%'+@keyword+'%'''
ELSE
SET @STR='SELECT ID,BookName,Writer,Publisher FROM BookStore WHERE CategoryID='+cast(@categoryid as varchar)+' AND '+@field+' LIKE ''%'+@keyword+'%'''
EXECUTE(@STR)
GO

[zjcxc]

没有进行数据类型转换,改一下:

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'xjtu_search'
AND type = 'P')
DROP PROCEDURE xjtu_search
GO

CREATE PROCEDURE xjtu_search
@field varchar(255),
@keyword varchar(255),
@categoryid int = 0
AS
DECLARE @STR varchar(1024)
IF (@categoryid=0)
SET @STR='SELECT ID,BookName,Writer,Publisher FROM BookStore WHERE '+@field+' LIKE ''%'+@keyword+'%'''
ELSE
SET @STR='SELECT ID,BookName,Writer,Publisher FROM BookStore WHERE CategoryID='+cast(@categoryid as varchar)+' AND '+@field+' LIKE ''%'+@keyword+'%''' --加上数据类型转换就行了.
EXECUTE(@STR)
GO

[yujohny]

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'xjtu_search'
AND type = 'P')
DROP PROCEDURE xjtu_search
GO

CREATE PROCEDURE xjtu_search
@field varchar(255),
@keyword varchar(255),
@categoryid int = 0
AS
DECLARE @STR varchar(1024)
IF (@categoryid=0)
SET @STR='SELECT ID,BookName,Writer,Publisher
FROM BookStore WHERE '+@field+' LIKE ''%'
+@keyword+'%'''
ELSE
SET @STR='SELECT ID,BookName,Writer,Publisher
FROM BookStore WHERE CategoryID='+cast(@categoryid as nvarchar)
+' AND '+@field+' LIKE ''%'+@keyword+'%'''
EXECUTE(@STR)
GO

[hanps]

数据类型转换

[wzh1215]

数据类型转换之后才能比较！

[sdhdy]

将数据类型转换一下！

[truesight]

我都复制进去试了不行！！！愁死我了

我的CategoryID字段是int型的啊

大家在自己的电脑上运行一下吧，谢谢，真的不行

我的环境：win2003+sql server2000（sp3）

[truesight]

好了好了，给分

不过有些糊涂，我的字段明明是int型，存储过程中的变量也是int型，输入的参数也是1，可是为什么要换成nvarchar啊