create proc proc_query_product
(@trade varchar(50),@timeinfo varchar(50))
as
declare @info varchar(100),@order varchar(50),@condition_vip varchar(100),@condition_company varchar(100),@sql_vip varchar(500),@sql_company varchar(800)
set @sql_company= 'select top 15 a,b,c,d from TProduct where '
set @sql_vip= 'select top 15 c,d,e,f from TProduct where '
set @order= ' order by g desc '
set @condition_company= ' and exists(select * from Tuser where (tUser.UserName=TProduct.UserName) and (TUser.userrole= '+ ' ' 'company ' ' '+ ')) '
set @condition_vip= ' and exists(select * from Tuser where (tUser.UserName=TProduct.UserName) and (TUser.userrole= '+ ' ' 'vip ' ' '+ ')) '
begin
select @info=
case @timeinfo
when '不限 ' then '(1=1) '
when '1天内的新消息 ' then '(datediff(dd,ppublishtime,getdate())=0) '
when '3天内的新消息 ' then '(datediff(dd,ppublishtime,getdate()) <=3) '
when '7天内的新消息 ' then '(datediff(dd,ppublishtime,getdate()) <=7) '
when '15天内的新消息 ' then '(datediff(dd,ppublishtime,getdate()) <=15) '
end
set @[email protected]_company+ ' (ptrade= '+ ' ' ' '[email protected]+ ' ' ' '+ ') and '[email protected][email protected][email protected]
set @[email protected]_vip+ ' (ptrade= '+ ' ' ' '[email protected]+ ' ' ' '+ ') and '[email protected][email protected][email protected]
begin
exec(@sql_company)
end
begin
exec(@sql_vip)
end
end
是用的 sql server 2000 @trade,@timeinfo是输入参数，[email protected]_company这个语句取出来的值跟设定的不一样 [email protected]_vip能取到值。
@condition_company的值变为: and exists(select * from Tuser where (tUser.UserName=TProduct.UserName) and (TUser.userrole= 'compan
@condition_vip的值为: and exists(select * from Tuser where (tUser.UserName=TProduct.UserName) and (TUser.userrole= 'vip '))是查询语句太长导致取不到值的么 期待解决
------解决方案--------------------
declare @info varchar(100),
@order varchar(50),
@condition_vip varchar(100),
@condition_company varchar(100),
@sql_vip varchar(8000), --修改为8000