Solved

sql - if exists

Posted on 2007-11-25
5
2,976 Views
Last Modified: 2012-06-27
I'm not sure my question will be able to be answered, but if anyone can help, it will be greatly appreciated.

I'm using a webservice to make calls into SalesForce.com API.  We use SalesForce as our CRM system.  SalesForce uses a proprietary version of SQL called SOQL.  It has limitations on joins, aggregates and other functions that regular SQL uses.  One limitation might be the use of the IF EXISTS statement, but I haven't found any documentation that says I can't use it.

I'm trying to do the following:

I want to write a function that checks if a record exists in one of the objects (tables) we have on their system and return true if the record exists and false if the record does not exist.

I want to use the function in an if statement and branch based on the return value of the function
 
If (function returns true) {
     do some processing here...
}
else {
     do some other processing...
}
 
The function would use the 'if exists' in the SOQL query to determine if a record exists in the contact object and return true or false.

IF EXISTS (Select c.Email, c.FirstName, c.HasOptedOutOfEmail, c.LastName from Contact c WHERE email = 'test@test.com')
SELET 1
ElSE
SELECT 2
 

I can't get this to work using SOQL, which might not allow the IF EXISTS statement.  Is there another method to accomplish the same thing?  Any code samples, examples would be helpful.
 
Thanks for any help.
0
Comment
Question by:-Dman100-
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20347814
CREATE FUNCTION dbo.checkExist (@email VARCHAR(1000) )
RETURNS INT
AS
BEGIN
      IF EXISTS (Select 1 from Contact WHERE email = @Email)
            RETURN(1)
      ElSE
            RETURN(2)

END

GO


And use like

if dbo.checkExist ('test@test.com')  =1
  print 'exists'
0
 

Author Comment

by:-Dman100-
ID: 20347901
Hi aneeshattingal,
Thanks for replying to my post  My apologies, I should have clarified.  I didn't mean a sql function.

I'm making calls thru a webservice API into SalesForce.com.  All I can do is use their proprietary SOQL language to query and retrieve data.

Does this make sense?
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20347906
if it doesn't exist then you are going to get a null recordset.  Now in MSSQL you can look at @@ROW_COUNT to find how many rows are returned - so if this is zero, then you've got your 'NOT EXIST' situation.

case @@ROW_COUNT when 0 then SELECT 1 else SELECT 2 end

but lets assume we've got BOTH hands tied behind our back.....and you don't have @@ROW_COUNT either.....in this case may something like this

select @flag as MIN(flag) from
 (select 1 as flag from contact where email = 'test@test.com"
union select 99 )

if @flag = 1 then SELECT 1
else SELECT 2

(above is all kinda psuedocode, as you don't specify how what 1 and 2 are that you are selecting, but I hope that as you are intermediate, you can either work it out yourself or post more details....
0
 
LVL 6

Accepted Solution

by:
PaultheBroker earned 250 total points
ID: 20347923
this will return to you either a 1 (if the record exits) or a 2 if it doesn't....it requires that SOQL supports the MIN function and the UNION function.

select min(flag) from (select 1 as flag from contact where email = 'test@test.com" union select 2)
0
 
LVL 7

Assisted Solution

by:Maverick_Cool
Maverick_Cool earned 250 total points
ID: 20350061
Select @rw=count(1) from Contact WHERE email = @Email
set @rw

if (@rw>0)
select 1
else
select 2
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

778 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