存储过程字符串丢失的问题
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 @sql_company=@sql_company+ ' (ptrade= '+ ' ' ' '+@trade+ ' ' ' '+ ') and '+@info+@condition_company+@order
set @sql_vip=@sql_vip+ ' (ptrade= '+ ' ' ' '+@trade+ ' ' ' '+ ') and '+@info+@condition_vip+@order
begin
exec(@sql_company)
end
begin
exec(@sql_vip)
end
end
是用的 sql server 2000 @trade,@timeinfo是输入参数，当执行以上存储过程时候发现@condition_company这个语句取出来的值跟设定的不一样 而@condition_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
@sql_company varchar(8000) --修改为8000
[解决办法]
@condition_company varchar(100)
改為
@condition_company varchar(8000)
[解决办法]
declare @info varchar(100),@order varchar(50),@condition_vip varchar(100),@condition_company varchar(100),@sql_vip varchar(500),@sql_company varchar(800)
---把这些都搞成varchar(8000)
[解决办法]
暈，慢了
另外，建議
declare @info varchar(100),@order varchar(50),@condition_vip varchar(100),@condition_company varchar(100),@sql_vip varchar(500),@sql_company varchar(800)
這幾個都修改長些
declare @info varchar(1000),@order varchar(1000),@condition_vip varchar(8000),@condition_company varchar(8000),@sql_vip varchar(8000),@sql_company varchar(8000)
