日期:2014-05-18  浏览次数:20643 次

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



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 output
as
declare @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 cyCms
execute 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 output
as
declare @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 cyCms
execute 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'

--楼主需要注意的是要将连接的变量看看是不是类型一致的。你的日期类型不能跟字符串连接啊