存储过程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+ ' ' ' '
set @Sql=@Sql+ ' Order By Model Desc '
Exec(@Sql)
GO
[解决办法]
set @sql=@sql+ 'a '
[解决办法]
@Sql=@Sql+ ' And ModelID= ' + cast(@ModelID as varchar(20))