Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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....
 

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 21:59pm
Edited 26-Feb-13 22:01pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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;
 

  Permalink  

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

  Print Answers RSS
0 OriginalGriff 365
1 Sergey Alexandrovich Kryukov 334
2 CPallini 275
3 DamithSL 230
4 Maciej Los 215
0 OriginalGriff 5,455
1 DamithSL 4,457
2 Maciej Los 3,885
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,115


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 27 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100