Solved

sql - if exists

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now