存储过程调用，格式转换有关问题

存储过程调用，格式转换问题
各位大神，地下是我写的一个存储过程，但在调用这个过程的时候出现错误

SQL code

ALTER PROCEDURE [dbo].[Up_Info_CheckInfo]    @chid int,@colidstr nvarchar(500)='',@Type int=1,@B_Date datetime='',@E_Date datetime='',@property varchar(50)='',@propertytype varchar(10)='=',@propertyvalue varchar(50)='',@recordcount int=0 outputasdeclare @wherestr nvarchar(2000)set @wherestr = '';if(@chid!=0)    set @wherestr = @wherestr+' co.chid='+convert(nvarchar(15),@chid)+' and ';if(@colidstr!='0')    set @wherestr = @wherestr+' i.colId in('+@colidstr+') and ';      if(@property!='' and @propertytype!='' and @propertyvalue!='')    set @wherestr = @wherestr+' i.'+@property+@propertytype+@propertyvalue+' and ';if(@Type!=1)   set @wherestr = @wherestr+'i.AddTime between '+@B_Date+' and '+@E_Date+' and 'set @wherestr = @wherestr+' i.status!=-1 and i.isdeleted=0'declare @selecttable nvarchar(500)set @selecttable = 'KyArticle i join kycolumn co on i.colid=co.colid join kychannel ch on ch.chid=co.chid ' declare @sqlstr Nvarchar(4000)set @sqlstr =' select PowerName as 机构,count(i.[id]) as 数量 from '+@selecttable+' join KyAdmin ad on i.UId=ad.UserId right join KyPowerGroup p on ad.GroupId=p.PowerId where '+@wherestr+' group by p.PowerId,p.PowerName'execute sp_executesql @sqlstr;执行存储过程的语句：use cyCmsexecute Up_Info_CheckInfo @chid=21,@colidstr='128,138,140,141',@Type=2,@B_Date='2012/4/26 14:17:48',@E_Date='2012/5/20 15:15:10'

错误信息：消息 241，级别 16，状态 1，过程 Up_Info_CheckInfo，第 48 行
从字符串向 datetime 转换时失败。错误出在@B_Date与@E_Date，求解决方案

[解决办法]

SQL code

ALTER PROCEDURE [dbo].[Up_Info_CheckInfo]@chid int,@colidstr nvarchar(500)='',@Type int=1,@B_Date datetime='',@E_Date datetime='',@property varchar(50)='',@propertytype varchar(10)='=',@propertyvalue varchar(50)='',@recordcount int=0 outputasdeclare @wherestr nvarchar(2000)set @wherestr = '';if(@chid!=0)    set @wherestr = @wherestr+' co.chid='+convert(nvarchar(15),@chid)+' and ';if(@colidstr!='0')    set @wherestr = @wherestr+' i.colId in('+@colidstr+') and ';      if(@property!='' and @propertytype!='' and @propertyvalue!='')    set @wherestr = @wherestr+' i.'+@property+@propertytype+@propertyvalue+' and ';if(@Type!=1)   set @wherestr = @wherestr+'i.AddTime between '''+@B_Date+''' and '''+@E_Date+''' and '  --注意需要添加''',提示含有单引号，来引用日期set @wherestr = @wherestr+' i.status!=-1 and i.isdeleted=0'declare @selecttable nvarchar(500)set @selecttable = 'KyArticle i join kycolumn co on i.colid=co.colid join kychannel ch on ch.chid=co.chid ' declare @sqlstr Nvarchar(4000)set @sqlstr =' select PowerName as 机构,count(i.[id]) as 数量 from '+@selecttable+' join KyAdmin ad on i.UId=ad.UserId right join KyPowerGroup p on ad.GroupId=p.PowerId where '+@wherestr+' group by p.PowerId,p.PowerName'execute sp_executesql @sqlstr;--执行存储过程的语句：use cyCmsexecute Up_Info_CheckInfo @chid=21,@colidstr='128,138,140,141',@Type=2,@B_Date='2012/4/26 14:17:48',@E_Date='2012/5/20 15:15:10'
[解决办法]
SQL code
 --这里修改下set @wherestr = @wherestr+'i.AddTime between '+convert(varchar(50),@B_Date)+' and '+convert(varchar(50),@E_Date)+' and '
[解决办法]
SQL code
   set @wherestr = @wherestr+'i.AddTime between '+cast(@B_Date as varchar(30))   +' and '+cast(@E_Date as varchar(30))+' and 'set @wherestr = @wherestr+' i.status!=-1 and i.isdeleted=0'--楼主需要注意的是要将连接的变量看看是不是类型一致的。你的日期类型不能跟字符串连接啊 

 

查看更多 下一篇