alter proc procGetHouse
@QuYuID int ,
@XiaoQu varchar(24) ,
@ShangQuanID int ,
@RoadName varchar(50) ,
@Address varchar(120),
@ZhuangXiuQinKuang varchar(12) ,
@Floor varchar(12) ,
@ZuJin int ,
@YaJinFangShi varchar(12) ,
@RuZhuTime datetime ,
@ZhuZhaiType varchar(12) ,
@ChaoXiang varchar(12) ,
@Area int ,
@Bed bit ,
@WaterHeater bit ,
@WashingMachine bit ,
@AirConditioning bit ,
@BingXiang bit ,
@TV bit,
@BroadBand bit ,
@Phone varchar(24) ,
@Owner varchar(12) ,
@EmpName varchar(12) ,
@ZhuFangShiXian int ,
@JiaJu bit ,
@Ting int ,
@Shi int ,
@Wei int ,
@Yang int
as
begindeclare @sql nvarchar(4000)
set @sql='select * from House where QuYuID='+ @QuYuID+''
if @XiaoQu !=null
set @sql = @sql + 'and XiaoQu=''' + @XiaoQu + ''''
if @ShangQuanID !=null
set @sql = @sql + 'and @ShangQuanID=' + @ShangQuanID + ''
if @RoadName !=null
set @sql = @sql + 'and @RoadName=''' + @RoadName + ''''
if @Address !=null
set @sql = @sql + 'and @Address=''' + @Address + ''''
if @ZhuangXiuQinKuang !=null
set @sql = @sql + 'and @ZhuangXiuQinKuang=''' + @ZhuangXiuQinKuang + ''''
if @Floor !=null
set @sql = @sql + 'and @Floor=''' + @Floor + ''''
if @ZuJin !=null
set @sql = @sql + 'and @ZuJin=''' + @ZuJin + ''''
if @YaJinFangShi !=null
set @sql = @sql + 'and @YaJinFangShi=''' + @YaJinFangShi + ''''
if @RuZhuTime !=null
set @sql = @sql + 'and @RuZhuTime=''' + @RuZhuTime + ''''
if @ZhuZhaiType !=null
set @sql = @sql + 'and @ZhuZhaiType=''' + @ZhuZhaiType + ''''
if @ChaoXiang !=null
set @sql = @sql + 'and @ChaoXiang=''' + @ChaoXiang + ''''
if @Area !=null
set @sql = @sql + 'and @Area=''' + @Area + ''''
if @Bed !=null
set @sql = @sql + 'and @Bed='' + @Bed + '''
if @WaterHeater !=null
set @sql = @sql + 'and @WaterHeater='' + @WaterHeater + '''
if @WashingMachine !=null
set @sql = @sql + 'and @WashingMachine='' + @WashingMachine + '''
if @AirConditioning !=null
set @sql = @sql + 'and @AirConditioning='' + @AirConditioning + '''
if @BingXiang !=null
set @sql = @sql + 'and @BingXiang='' + @BingXiang + '''
if @TV !=null
set @sql = @sql + 'and @TV='' + @TV + '''
if @BroadBand !=null
set @sql = @sql + 'and @BroadBand='' + @BroadBand + '''
if @Phone !=null
set @sql = @sql + 'and @Phone=''' + @Phone + ''''
if @Owner !=null
set @sql = @sql + 'and @Owner=''' + @Owner + ''''
if @EmpName !=null
set @sql = @sql + 'and @EmpName=''' + @EmpName + ''''
if @ZhuFangShiXian !=null
set @sql = @sql + 'and @ZhuFangShiXian=''' + @ZhuFangShiXian + ''''
if @JiaJu !=null
set @sql = @sql + 'and @JiaJu='' + @JiaJu + '''
if @Ting !=null
set @sql = @sql + 'and @Ting='' + @Ting + '''
if @Shi !=null
set @sql = @sql + 'and @Shi='' + @Shi + '''
if @Wei !=null
set @sql = @sql + 'and @Wei='' + @Wei + '''
if @Yang !=null
set @sql = @sql + 'and @Yang='' + @Yang + '''print (@sql)
exec (@sql)end
exec procGetHouse 1,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
执行了弹出这样的错误
@QuYuID int ,
@XiaoQu varchar(24) ,
@ShangQuanID int ,
@RoadName varchar(50) ,
@Address varchar(120),
@ZhuangXiuQinKuang varchar(12) ,
@Floor varchar(12) ,
@ZuJin int ,
@YaJinFangShi varchar(12) ,
@RuZhuTime datetime ,
@ZhuZhaiType varchar(12) ,
@ChaoXiang varchar(12) ,
@Area int ,
@Bed bit ,
@WaterHeater bit ,
@WashingMachine bit ,
@AirConditioning bit ,
@BingXiang bit ,
@TV bit,
@BroadBand bit ,
@Phone varchar(24) ,
@Owner varchar(12) ,
@EmpName varchar(12) ,
@ZhuFangShiXian int ,
@JiaJu bit ,
@Ting int ,
@Shi int ,
@Wei int ,
@Yang int
as
begindeclare @sql nvarchar(4000)
set @sql='select * from House where QuYuID='+ @QuYuID+''
if @XiaoQu !=null
set @sql = @sql + 'and XiaoQu=''' + @XiaoQu + ''''
if @ShangQuanID !=null
set @sql = @sql + 'and @ShangQuanID=' + @ShangQuanID + ''
if @RoadName !=null
set @sql = @sql + 'and @RoadName=''' + @RoadName + ''''
if @Address !=null
set @sql = @sql + 'and @Address=''' + @Address + ''''
if @ZhuangXiuQinKuang !=null
set @sql = @sql + 'and @ZhuangXiuQinKuang=''' + @ZhuangXiuQinKuang + ''''
if @Floor !=null
set @sql = @sql + 'and @Floor=''' + @Floor + ''''
if @ZuJin !=null
set @sql = @sql + 'and @ZuJin=''' + @ZuJin + ''''
if @YaJinFangShi !=null
set @sql = @sql + 'and @YaJinFangShi=''' + @YaJinFangShi + ''''
if @RuZhuTime !=null
set @sql = @sql + 'and @RuZhuTime=''' + @RuZhuTime + ''''
if @ZhuZhaiType !=null
set @sql = @sql + 'and @ZhuZhaiType=''' + @ZhuZhaiType + ''''
if @ChaoXiang !=null
set @sql = @sql + 'and @ChaoXiang=''' + @ChaoXiang + ''''
if @Area !=null
set @sql = @sql + 'and @Area=''' + @Area + ''''
if @Bed !=null
set @sql = @sql + 'and @Bed='' + @Bed + '''
if @WaterHeater !=null
set @sql = @sql + 'and @WaterHeater='' + @WaterHeater + '''
if @WashingMachine !=null
set @sql = @sql + 'and @WashingMachine='' + @WashingMachine + '''
if @AirConditioning !=null
set @sql = @sql + 'and @AirConditioning='' + @AirConditioning + '''
if @BingXiang !=null
set @sql = @sql + 'and @BingXiang='' + @BingXiang + '''
if @TV !=null
set @sql = @sql + 'and @TV='' + @TV + '''
if @BroadBand !=null
set @sql = @sql + 'and @BroadBand='' + @BroadBand + '''
if @Phone !=null
set @sql = @sql + 'and @Phone=''' + @Phone + ''''
if @Owner !=null
set @sql = @sql + 'and @Owner=''' + @Owner + ''''
if @EmpName !=null
set @sql = @sql + 'and @EmpName=''' + @EmpName + ''''
if @ZhuFangShiXian !=null
set @sql = @sql + 'and @ZhuFangShiXian=''' + @ZhuFangShiXian + ''''
if @JiaJu !=null
set @sql = @sql + 'and @JiaJu='' + @JiaJu + '''
if @Ting !=null
set @sql = @sql + 'and @Ting='' + @Ting + '''
if @Shi !=null
set @sql = @sql + 'and @Shi='' + @Shi + '''
if @Wei !=null
set @sql = @sql + 'and @Wei='' + @Wei + '''
if @Yang !=null
set @sql = @sql + 'and @Yang='' + @Yang + '''print (@sql)
exec (@sql)end
exec procGetHouse 1,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
执行了弹出这样的错误
将 varchar 值 'select * from House where QuYuID=' 转换为数据类型为 int 的列时发生语法错误。
我写的这个好像有点错,你看下 if @BroadBand !=null
set @sql = @sql + 'and @BroadBand='' + @BroadBand + '''@BroadBand是bit型
set @sql = @sql + 'and @BroadBand=' + ltrim(@BroadBand)
@XiaoQu varchar(24) ,
@ShangQuanID int ,
@RoadName varchar(50) ,
@Address varchar(120),
@ZhuangXiuQinKuang varchar(12) ,
@Floor varchar(12) ,
@ZuJin int ,
@YaJinFangShi varchar(12) ,
@RuZhuTime datetime ,
@ZhuZhaiType varchar(12) ,
@ChaoXiang varchar(12) ,
@Area int ,
@Bed bit ,
@WaterHeater bit ,
@WashingMachine bit ,
@AirConditioning bit ,
@BingXiang bit ,
@TV bit,
@BroadBand bit ,
@Phone varchar(24) ,
@Owner varchar(12) ,
@EmpName varchar(12) ,
@ZhuFangShiXian int ,
@JiaJu bit ,
@Ting int ,
@Shi int ,
@Wei int ,
@Yang int
这些参数是int类型的 全部改成 varchar类型的就ok了
转换为 int 在 连接不要用那么多 引号 太多了,麻烦,
例:
set @sql='select * from House where QuYuID='+ str(@QuYuID)
if @XiaoQu !=null
set @sql = @sql + 'and XiaoQu=' + @XiaoQu