Solved

Check for existing data and then insert if not existing

Posted on 2009-04-09
1
679 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

717 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