CREATE PROCEDURE [selectvipcompany]
@username varchar(50),
@companyname varchar(50),
@endtime varchar(1)
AS
declare @sql varchar(1000)
set @sql=''
if (@username <>null and @username <>'' )
set @sql=@sql+'username like '+char(39)+'%'+@username+'%'+char(39)
if (@companyname <>null and @companyname <>'' and @sql <>'')
set @sql=@sql+' and companyname like '+char(39)+'%'+@companyname+'%'+char(39)
else if (@companyname <>null and @companyname <>'' and @sql='')
set @sql=@sql+'companyname like'+char(39)+' %'+@companyname+'%'+char(39)
if (@endtime='1' and @sql <>'')
set @sql=@sql+' and '+datediff+char(40)+'endviptime,'+getDate()+char(41)+' <0'
else if(@endtime='1' and @sql='')
set @sql=@sql+datediff+char(40)+'endviptime,'+getDate()+char(41)+' <0'
if (@endtime='0' and @sql <>'')
set @sql=@sql+' and '+datediff+char(40)+'endviptime,'+getDate()+char(41)+'=0'
else if(@endtime='0' and @sql='')
set @sql=@sql+datediff+char(40)+'endviptime,'+getDate()+char(41)+'=0'
if (@sql <>'')
set @sql='select username,companyname,createtime,endviptime,vb_id from admin_vcompanybase where isvip=1 and '+@sql
else
set @sql='select username,companyname,createtime,endviptime,vb_id from admin_vcompanybase mowhere isvip=1'
exec(@sql)
GO
错误提示 datediff列名无效
这是一个搜索的过程,条件有三个username用户名,companyname公司名,endtime是否是过期用户 username,companyname是可输入搜索条件,endtime如果是0表是今天到期,endtime如是1表示已经过期,2表示正常,默认是2
数据库表是到期列名是 endviptime 现在是datediff(endvip,getdate())判断时间,但是出错 提示datediff列名无效 请各位帮我看看,谢谢!!!
@username varchar(50),
@companyname varchar(50),
@endtime varchar(1)
AS
declare @sql varchar(1000)
set @sql=''
if (@username <>null and @username <>'' )
set @sql=@sql+'username like '+char(39)+'%'+@username+'%'+char(39)
if (@companyname <>null and @companyname <>'' and @sql <>'')
set @sql=@sql+' and companyname like '+char(39)+'%'+@companyname+'%'+char(39)
else if (@companyname <>null and @companyname <>'' and @sql='')
set @sql=@sql+'companyname like'+char(39)+' %'+@companyname+'%'+char(39)
if (@endtime='1' and @sql <>'')
set @sql=@sql+' and '+datediff+char(40)+'endviptime,'+getDate()+char(41)+' <0'
else if(@endtime='1' and @sql='')
set @sql=@sql+datediff+char(40)+'endviptime,'+getDate()+char(41)+' <0'
if (@endtime='0' and @sql <>'')
set @sql=@sql+' and '+datediff+char(40)+'endviptime,'+getDate()+char(41)+'=0'
else if(@endtime='0' and @sql='')
set @sql=@sql+datediff+char(40)+'endviptime,'+getDate()+char(41)+'=0'
if (@sql <>'')
set @sql='select username,companyname,createtime,endviptime,vb_id from admin_vcompanybase where isvip=1 and '+@sql
else
set @sql='select username,companyname,createtime,endviptime,vb_id from admin_vcompanybase mowhere isvip=1'
exec(@sql)
GO
错误提示 datediff列名无效
这是一个搜索的过程,条件有三个username用户名,companyname公司名,endtime是否是过期用户 username,companyname是可输入搜索条件,endtime如果是0表是今天到期,endtime如是1表示已经过期,2表示正常,默认是2
数据库表是到期列名是 endviptime 现在是datediff(endvip,getdate())判断时间,但是出错 提示datediff列名无效 请各位帮我看看,谢谢!!!
改成
set @sql=@sql+' and datediff(dd你要计算返回的是天数,是小时还是秒,选一个. 不同你可以查看sql帮助文档,endviptime,Convert(DateTime,'''+convert(nvarchar(10),getDate())+''') <0'
d表示只比较天
参考
http://msdn.microsoft.com/en-us/library/ms189794.aspx
你print (@sql) 打印出来 的sql语句是拼接的, 然后你另外执行这段print 出来的 代码 就容易知道问题处在哪里了
打印出来了
select username,companyname,createtime,endviptime,vb_id from admin_vcompanybase where isvip=1 and username like '%2%' and companyname like '%2%' and datediff(day,endviptime,getDate()) <0 这样做了,但没有查出值来, datediff(day,endviptime,getDate())=0 <0 >0都试过了,还是没有,endviptime时间也改过期,没过期的时间都试过了把and companyname like '%2%' and datediff(day,endviptime,getDate()) <0 去掉就有值了
这个中间是否应该改成OR
and (username like '%2%' OR companyname like '%2%')