Solved

sql - if exists

Posted on 2007-11-25
5
2,983 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 2 39
convert null in sql server 12 45
"Emulate" TAB key when press Enter Key 3 44
SSRS Deployment problem 5 63
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

789 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