存储过程int连接单引号问题,
存储过程如下：
Create   PROC   GetModelList
@CategoryID   int,
@ModelID   int,
@Model   varchar(50)

As
Declare   @Sql   varchar(200)
Set   @Sql   =   'Select   ModelID,Model,Models.CategoryID   As   CateID,CategoryName_CH,Pdf,InstallPicPath, '
+ 'ProPicPath,AssmParaPicPath,ModelDesc_CN,ModelDesc_EN,ModelDesc   From   Models,Categories   '
+ 'Where   Models.CategoryID=Categories.CategoryID '

if(@CategoryId   <>   0)
@Sql=@Sql   +   '   And   Models.CategoryID= ' "+@CategoryID+ " "
這出錯，Incorrect   syntax   near   '@Sql '.
Cannot   use   empty   object   or   column   names.   Use   a   single   space   if   necessary.

if(@ModelID <>   0)
@Sql=@Sql+   '   And   ModelID= ' "+@ModelID+ " "

if(@Model   <> ' ')
@Sql=@Sql+ '   And   Model= ' ' '+@Model+ ' ' ' '
這出錯，Incorrect   syntax   near   '@Sql '.
@Sql=@Sql+ '   Order   By   Model   Desc '

Exec(@Sql)
GO

數字要怎么連接啊？

------解决方案--------------------
@Sql=@Sql + ' And Models.CategoryID= ' + cast(@CategoryID as varchar(2))

------解决方案--------------------
先要转换成varchar
------解决方案--------------------
@Sql=@Sql + ' And Models.CategoryID= ' + CONVERT(varchar(20),@CategoryID )


------解决方案--------------------
create PROC GetModelList
@CategoryID int,
@ModelID int,
@Model varchar(50)
as

set quoted_identifier off

Declare @Sql varchar(500)
Set @Sql = 'Select ModelID,Model,Models.CategoryID As CateID,CategoryName_CH,Pdf,InstallPicPath, '
+ 'ProPicPath,AssmParaPicPath,ModelDesc_CN,ModelDesc_EN,ModelDesc From Models,Categories '
+ 'Where Models.CategoryID=Categories.CategoryID '

if(@CategoryId <> 0)
set @Sql=@Sql + ' And Models.CategoryID= '+ltrim(@CategoryID)


if(@ModelID <> 0)
set @Sql=@Sql+ ' And ModelID= '+ltrim(@ModelID)

if(@Model <> ' ')
set @Sql=@Sql+ " And Modelid= ' "+@Model+ " ' "

set @Sql=@Sql+ ' Order By Model Desc '

Exec(@Sql)


set quoted_identifier on
GO
------解决方案--------------------
Create PROC GetModelList
@CategoryID int,
@ModelID int,
@Model varchar(50)

As
Declare @Sql varchar(200)
Set @Sql = 'Select ModelID,Model,Models.CategoryID As CateID,CategoryName_CH,Pdf,InstallPicPath, '
+ 'ProPicPath,AssmParaPicPath,ModelDesc_CN,ModelDesc_EN,ModelDesc From Models,Categories '
+ 'Where Models.CategoryID=Categories.CategoryID '

if(@CategoryId <> 0)
set @Sql=@Sql + ' And Models.CategoryID= ' "+rtrim(cast(@CategoryID as varchar))+ " "

if(@ModelID <> 0)
set @Sql=@Sql+ ' And ModelID= ' "+rtrim(cast(@ModelID as varchar))+ " "

if(@Model <> ' ')
set @Sql=@Sql+ ' And Model= ' ' '+@Model+ ' ' ' '