首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

存储过程int连接单引号有关问题

2012-03-29 
存储过程int连接单引号问题,存储过程如下:CreatePROCGetModelList@CategoryIDint,@ModelIDint,@Modelvarch

存储过程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))

热点排行