?
Solved

MS SQL 2008 If Else Stored Procedure

Posted on 2010-01-08
4
Medium Priority
?
333 Views
Last Modified: 2012-05-08
Hello Experts,

Happy New Year, Quick question, I need to find a way or returning data from 1 of 2 tables (live data/Expired Data) I'm looking for the best way of building an SP like below -

Many thanks
G
IF Exists
SELECT    
AD.JBAID, 
Lower(AD.JBATitle) AS JBATitle,  
AD.JBALocation,  
AD.JBAPayRate,  
AD.JBAReference, 
CONVERT(CHAR(11),JBADatePosted,106) AS JBADatePosted   
FROM dbo.JBAdvert AD  
WHERE JBAID = xx AND JBAEmployeeID = ? 
Else
SELECT    
AD.JBAID, 
Lower(AD.JBATitle) AS JBATitle,  
AD.JBALocation,  
AD.JBAPayRate,  
AD.JBAReference, 
CONVERT(CHAR(11),JBADatePosted,106) AS JBADatePosted   
FROM dbo.JBExpiredAdvert AD  
WHERE JBAID = xx AND JBAEmployeeID = ? 
End if

Open in new window

0
Comment
Question by:garethtnash
  • 2
4 Comments
 
LVL 11

Expert Comment

by:rajvja
ID: 26208724
Hi,

 IF EXISTS...
what are you doing in the true condition??
Your script doesn't show anything
On what basis you retrieve data from Live/expired tables?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 26208736
something like this:


SET NOCOUNT ON
DECLARE @res TABLE (JBAID int, JBATitle varchar(100) ... etc .. )

INSERT INTO @res SELECT    
AD.JBAID,
Lower(AD.JBATitle) AS JBATitle,  
AD.JBALocation,  
AD.JBAPayRate,  
AD.JBAReference,
CONVERT(CHAR(11),JBADatePosted,106) AS JBADatePosted  
FROM dbo.JBAdvert AD  
WHERE JBAID = xx AND JBAEmployeeID = ?

IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO @res
   SELECT    
AD.JBAID,
Lower(AD.JBATitle) AS JBATitle,  
AD.JBALocation,  
AD.JBAPayRate,  
AD.JBAReference,
CONVERT(CHAR(11),JBADatePosted,106) AS JBADatePosted  
FROM dbo.JBExpiredAdvert AD  
WHERE JBAID = xx AND JBAEmployeeID = ?

END

select * FROM @res

0
 
LVL 11

Assisted Solution

by:rajvja
rajvja earned 1000 total points
ID: 26208866
Hi
What you are doing is right way. Why do you think in a best way than the current?
Are you facing any problems doing this way?
0
 

Author Comment

by:garethtnash
ID: 26208975
Thank you All for your help, on reflection I'm going to use If Else statements on the Web Server as I have read that temporary tables utilise a lot of resources.
Please let me know if this is a mistake. in the mean time thank you & Happy 2010
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question