Solved

Check for existing data and then insert if not existing

Posted on 2009-04-09
1
676 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 142

Accepted Solution

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

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 41
Excel conversion issue with Sql server 14 54
Alter a column in sql 34 30
Problem with SQL Script - Cannot call methods on char 2 21
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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