Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql - if exists

Posted on 2007-11-25
5
Medium Priority
?
3,083 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 1000 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 1000 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Screencast - Getting to Know the Pipeline
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…

580 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