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

x
?
Solved

Check for existing data and then insert if not existing

Posted on 2009-04-09
1
Medium Priority
?
684 Views
Last Modified: 2012-08-13
I have this sp that is checking for an existing POC and if the POC is not there, enters relevant info.  I'm pulling an error in the syntax...thx

CREATE PROCEDURE [dbo].[usp_addPointOfContact1]
 
(
@poc_first_name varchar(50) = 'test',
@poc_last_name varchar (50) = 'tester',
@poc_email_address varchar(50)= 'test@gmail.com',
@RetStatus tinyint = 0 output
)
as
declare  @trancount    int

 
 
 
set @trancount = @@trancount
 
begin try
 
if @trancount = 0
     begin transaction
else
     save transaction addpoc
 
IF Not Exists (select last_name, first_name, email)
from tbl_POC
where last_name = @poc_last_name and first_name = @poc_first_name  and email = @poc_email_address)  

Insert into tbl_POC (First_Name, Last_Name, Email)
values (@poc_first_name,@poc_last_name,@poc_email_address)
 
if @trancount = 0
     commit
end try
 
begin catch
     declare
            @return int
           ,@error_message nvarchar(2048)
           ,@error_severity int
           ,@error_state int
           
     select
            @error_message = error_message()
           ,@error_severity = error_severity()
           ,@error_state = error_state()
 
     
 
     if @trancount = 0
          rollback transaction
    else
        if xact_state()<>-1
           rollback transaction AddPOC
 
     raiserror(@error_message,@error_state,@error_state)
     
end catch

if @@rowcount > 0
set @retstatus=1
0
Comment
Question by:Glen_D
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24108805
remove the trailing ) from this line:

IF Not Exists (select last_name, first_name, email)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

564 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