如何在函数SQL中传递标志

sql sql-server sql-server-2008

68 观看

1回复

11 作者的声誉

如何在函数中传递标志?

我有一个如下功能:

Alter function datedifferences (@leadid int,@businessid int,@flag int)
Returns table
as
Return
(       
    case when @flag=1
         then
             select case when datediff(MI,Lastactivedate,getdate()) < 1
                         then 'Online' 
                         when cast(datediff(MI,Lastactivedate,getdate()) as varchar(10)) < 60  
                         then cast( datediff(MI,Lastactivedate,getdate()) as varchar(10)) + 'Mins ago'
                         when cast( datediff(MI,Lastactivedate,getdate()) as varchar(10)) >= 60 
                              and cast( datediff(MI,Lastactivedate,getdate()) as varchar(10)) <= 1440  
                         then cast( datediff(MI,Lastactivedate,getdate())/60 as varchar(10)) + 'Hour ago'
                         when cast( datediff(MI,Lastactivedate,getdate()) as varchar(10)) > 1440  
                         then cast( datediff(MI,Lastactivedate,getdate()) / 1440 as varchar(10)) + 'day ago'
                    end as lastactivedate 
              from userdetails ud  
              join enquiry eq on ud.contentid=eq.UserId 
              where eq.LeadId=@leadid and eq.BusinessId=@businessid 
         else
              select top 1 * from business where businessid=@businessid
         end as flag)
作者: Sandeep Singh 的来源 发布者: 2017 年 9 月 15 日

回应 1


0

1 作者的声誉

您可以将“ UNION”与“ Where”条件结合使用

ALTER function datedifferences (@leadid int,@businessid int,@flag int)
RETURNS TABLE
AS RETURN
( 
    select case when datediff(MI,Lastactivedate,getdate())<1 then 'Online' 
                when cast( datediff(MI,Lastactivedate,getdate()) as varchar(10))<60 then cast( datediff(MI,Lastactivedate,getdate()) as varchar(10))+ 'Mins ago' 
                when cast( datediff(MI,Lastactivedate,getdate()) as varchar(10))>=60 and cast( datediff(MI,Lastactivedate,getdate()) as varchar(10))<=1440 then cast( datediff(MI,Lastactivedate,getdate())/60 as varchar(10)) + 'Hour ago' 
                when cast( datediff(MI,Lastactivedate,getdate()) as varchar(10))>1440 then cast( datediff(MI,Lastactivedate,getdate())/1440 as varchar(10)) + 'day ago' 
            end as lastactivedate 
    from userdetails ud join enquiry eq on ud.contentid=eq.UserId 
    where eq.LeadId=@leadid and eq.BusinessId=@businessid and @flag=1
 UNION ALL
    SELECT top 1 lastactivedate from business where businessid=@businessid and @flag<>1
)

要么

ALTER function datedifferences (@leadid int,@businessid int,@flag int)
RETURNS TABLE
AS RETURN
( 
 SELECT 1 AS A,2 AS B,3 AS C WHERE @flag<>1
 UNION ALL
 SELECT 4 AS A,5 AS B,6 AS C WHERE @flag=1
)
作者: Hitesh 发布者: 2017 年 9 月 15 日
32x32