How to use conditions(if else or case) in a sql query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

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....

SQL

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

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....

Posted 26-Feb-13 20:59pm

Vicky.tts

Updated 26-Feb-13 21:01pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MalwareTrojan · Answer 1 · 2013-02-26T21:04:00

Solution 1

http://msdn.microsoft.com/en-us/library/ms182717.aspx[^]

Try another 1:

http://stackoverflow.com/questions/13526592/using-if-else-in-sql-select-statement[^]

Posted 26-Feb-13 21:04pm

MalwareTrojan

Just eg · Answer 2 · 2013-02-26T21:33:00

example

SQL

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;