如何在SQL中使用带语句的switch语句 [英] how to use switch statement with case in SQL
本文介绍了如何在SQL中使用带语句的switch语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
HEllo团队,
我有代码AS跟随:
我想在以下SP中使用Switch语句。
ALTER PROCEDURE [dbo]。[DEORecordCount]
@Case int = 0
AS BEGIN
if(@ Case = 1)
BEGIN
选择U.NETWORKID,将(c.ID)计算为来自CUSTOMERLOANDATA C的DEORECORDCOUNTDONE
(nolock)
内部加入USERMASTER U(nolock) )在U.Id = C.CREATEDBY
where convert(varchar(10),c.createdon,120)= CONVERT(VARCHAR(10),'2014-02-28 12:48:57.000',120)
- 其中convert(varchar(10),c.createdon,120)= CONVERT(VARCHAR(10),GETDATE(),120)
group by U.NETWORKID
order by U .NETWORKID
END
ELSE IF(@ Case = 2)
BEGIN
------------------ FOR PENDING-- ----------------
从CUSTOMERLOANOCR C(nolock)中选择U.NETWORKID,将(c.ID)计为DEORecordCountPending
内部加入USERMASTER U(nolock)在U.Id = C.ASSIGNEDTO
,其中
c.createdon< ='2014-06 -01'
- 其中convert(varchar(10),c.createdon,120)< = CONVERT(VARCHAR(10),GETDATE(),120)
group by U.NETWORKID
订购U.NETWORKID
结束
结束
解决方案
你应该使用类似的例子下面:
声明 @locationType varchar ( 50 );
声明 @ locationID int ;
SELECT column1,column2
FROM viewWhatever
WHERE
@ locationID =
CASE @ locationType
WHEN ' location' 那么 account_location
WHEN ' area' 那么 xxx_location_area
WHEN ' division' 那么 xxx_location_division
END
无需使用if(@ Case = 1)等
你可以使用以下条件,不需要案例陈述
选择 U.NETWORKID,count(c.ID) as DEORECORDCOUNTDONE
来自 CUSTOMERLOANDATA C( nolock )
内部 加入 USERMASTER U( nolock ) U.Id = C .CREATEDBY
其中
(@ Case = 1 AND convert ( varchar ( 10 ),c.createdon, 120 )= CONVERT ( VARCHAR ( 10 ),' 2014-02-28 12:48:57.000', 120 ))
OR
(@ Case = 2 AND c.createdon< = ' 2014-06-01')
group by U.NETWORKID
订单 按 U.NETWORKID
参考:如何在T-SQL中使用switch-case语句..? [ ^ 一>
HEllo Team,
I have the Code AS Follows:
I want to use Switch statement in Following SP.
ALTER PROCEDURE [dbo].[DEORecordCount] @Case int=0 AS BEGIN if(@Case=1) BEGIN select U.NETWORKID, count(c.ID) as DEORECORDCOUNTDONE from CUSTOMERLOANDATA C (nolock) Inner Join USERMASTER U (nolock) on U.Id = C.CREATEDBY where convert(varchar(10),c.createdon,120)= CONVERT(VARCHAR(10),'2014-02-28 12:48:57.000',120) --where convert(varchar(10),c.createdon,120)= CONVERT(VARCHAR(10), GETDATE(),120) group by U.NETWORKID order by U.NETWORKID END ELSE IF(@Case=2) BEGIN ------------------FOR PENDING------------------ select U.NETWORKID, count(c.ID) as DEORecordCountPending from CUSTOMERLOANOCR C (nolock) Inner Join USERMASTER U (nolock) on U.Id = C.ASSIGNEDTO where c.createdon <= '2014-06-01 ' --where convert(varchar(10),c.createdon,120)<= CONVERT(VARCHAR(10), GETDATE(),120) group by U.NETWORKID order by U.NETWORKID END end
解决方案
You should use like example below:
declare @locationType varchar(50); declare @locationID int; SELECT column1, column2 FROM viewWhatever WHERE @locationID = CASE @locationType WHEN 'location' THEN account_location WHEN 'area' THEN xxx_location_area WHEN 'division' THEN xxx_location_division END
No need to use if(@Case=1) etc
you can use where condition as below, no need case statement
select U.NETWORKID, count(c.ID) as DEORECORDCOUNTDONE from CUSTOMERLOANDATA C (nolock) Inner Join USERMASTER U (nolock) on U.Id = C.CREATEDBY where (@Case=1 AND convert(varchar(10),c.createdon,120)= CONVERT(VARCHAR(10),'2014-02-28 12:48:57.000',120)) OR (@Case=2 AND c.createdon <= '2014-06-01') group by U.NETWORKID order by U.NETWORKID
Refer: How to use switch - case statement in T-SQL..?[^]
这篇关于如何在SQL中使用带语句的switch语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文