如何在sql查询中使用条件（if else或case） [英] How to use conditions(if else or case) in a sql query
本文介绍了如何在sql查询中使用条件（if else或case）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
大家好，
我在商店程序中遇到sql查询问题，如
如果select查询返回的结果为0我不得不显示一些消息。
我也提供代码（查询sp）以便更好地理解....
Hi all,
I am facing a problem in sql query in store procedures like
if a select query returns 0 results than i had to show some message.
I am also providing the code(query of sp)for better understanding....
Create PROCEDURE [dbo].[SendMail_PendingDocs]
-- Add the parameters for the stored procedure here
AS
BEGIN
DECLARE @xmlBIS NVARCHAR(MAX)
DECLARE @xmlCRC NVARCHAR(MAX)
DECLARE @xmlQSISO NVARCHAR(MAX)
DECLARE @xmlISO NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @CURRDATE datetime;
set @CURRDATE=getdate();
declare @beforedate datetime
set @beforedate = dateadd(d,10, getdate());
--For BIS Documents--
SET @xmlBIS = CAST(( SELECT [ar_agency_id] AS 'td','',[ar_bis_val_dt] AS 'td'
FROM beesl.dbo.agency_reg_docs where ar_bis_val_dt between @CURRDATE and @beforedate
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html>
<body>
<div>
<table cellpadding="4" cellspacing="1" bgcolor=#FFFFFF border=1 rules=none frame=box >
<tr >
<th style=border:1px solid #000000; align=left bgcolor=#c2c2c2> Agency ID </th>
<th style=border:1px solid #000000; align=left bgcolor=#c2c2c2> Validity Date </th>
</tr>'
DECLARE @TMbody NVARCHAR(MAX)
--For Trade Mark CRC--
SET @xmlCRC = CAST(( SELECT [ar_agency_id] AS 'td','',[ar_tm_crc_val_dt] AS 'td'
FROM beesl.dbo.agency_reg_docs where ar_tm_crc_val_dt between @CURRDATE and @beforedate
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @TMbody ='<html>
<body>
<div>
<table cellpadding="4" cellspacing="1" bgcolor=#FFFFFF border=1 rules=none frame=box >
<tr >
<th style=border:1px solid #000000; align=left bgcolor=#c2c2c2> Agency ID </th>
<th style=border:1px solid #000000; align=left bgcolor=#c2c2c2> Validity Date </th>
</tr>'
DECLARE @QSISObody NVARCHAR(MAX)
--For ISO CRC--
SET @xmlQSISO = CAST(( SELECT [ar_agency_id] AS 'td','',[ar_qs_iso_val_dt] AS 'td'
FROM beesl.dbo.agency_reg_docs where ar_qs_iso_val_dt between @CURRDATE and @beforedate
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @QSISObody ='<html>
<body>
<div>
<table cellpadding="4" cellspacing="1" bgcolor=#FFFFFF border=1 rules=none frame=box >
<tr >
<th style=border:1px solid #000000; align=left bgcolor=#c2c2c2> Agency ID </th>
<th style=border:1px solid #000000; align=left bgcolor=#c2c2c2> Validity Date </th>
</tr>'
DECLARE @ISObody NVARCHAR(MAX)
--For ISO CRC--
SET @xmlISO = CAST(( SELECT [ag_id] AS 'td','',[ag_mfr_iso_val_dt] AS 'td'
FROM beesl.dbo.agency_mfr where ag_mfr_iso_val_dt between @CURRDATE and @beforedate
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @ISObody ='<html>
<body>
<div>
<table cellpadding="4" cellspacing="1" bgcolor=#FFFFFF border=1 rules=none frame=box >
<tr >
<th style=border:1px solid #000000; align=left bgcolor=#c2c2c2> Agency ID </th>
<th style=border:1px solid #000000; align=left bgcolor=#c2c2c2> Validity Date </th>
</tr>'
--SET @body = @body + @xml +'</table></div></body></html>' + @TMbody + @xmlApp +'</table></div></body></html>'
SET @body = @body + @xmlBIS +'</table></div></body></html>'+ @TMbody + @xmlCRC +'</table></div></body></html>'
+ @QSISObody + @xmlQSISO +'</table></div></body></html>'
+ @ISObody + @xmlISO +'</table></div></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Mailing Profile',
@recipients='Email ID',
@subject='Server Message',
@importance= High,
@body = @body,
@body_format ='HTML';
END
我想把所有（选择） ）在IF ELSE条件下进行查询，这样如果查询返回的内容不是我可以为此添加一些消息，如无数据显示。
提前致谢... ..
I want to put all the(Select) query in IF ELSE condition so that if a query returns nothing than i can put some message for that like "No data to Display".
Thanks in advance....
推荐答案
http：// msdn .microsoft.com / zh-CN / library / ms182717.aspx [ ^ ]
尝试另外1：
http://stackoverflow.com/questions/13526592/using-if-else-in- sql-select-statement [ ^ ]
示例
example
USE [GJPNS_OKU]
GO
/****** this function use case statement to select record from any table **/
CREATE FUNCTION [dbo].[0901_TJJBT]( @STTJBT tinyint
,@KDSATKER nvarchar(10)
, @KDGOL tinyint
, @KDJBT tinyint)
RETURNS Money AS
BEGIN
DECLARE @Tjg money
SET @Tjg=CASE @STTJBT
WHEN 1 THEN (SELECT TOP 1 [TUNJANGAN] FROM [0901_TJE] WHERE [KD ESL]=@KDJBT)
WHEN 2 THEN (SELECT TOP 1 [TUNJANGAN] FROM [0901_TJF] WHERE (LEFT([KD SATKER],LEN(@KDSATKER))=@KDSATKER) AND ([KD FNG]=@KDJBT) AND ([KD GOL]=@KDGOL))
WHEN 3 THEN (SELECT TOP 1 [TUNJANGAN] FROM [0901_TJU] WHERE ([KD GOL]=@KDGOL))
ELSE 0 END
RETURN ISNULL(@Tjg,0)
End
GO
/****** IIF Function like IIF function on VB **/
CREATE FUNCTION [dbo].[eIIF] (@Opr0 sql_variant,@Opr1 sql_variant, @param2 sql_variant,@param3 sql_variant)
RETURNS sql_variant
AS BEGIN
DECLARE @Ret sql_variant
SET @Ret=CASE WHEN (@Opr0=@Opr1) THEN (@param2) ELSE (@param3) END
RETURN @Ret
END
GO
/****** Test IIF function**/
SELECT dbo.eIIF(1,1,'True','False') as expr1;
这篇关于如何在sql查询中使用条件（if else或case）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
