?
Solved

Check for existing data and then insert if not existing

Posted on 2009-04-09
1
Medium Priority
?
680 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 2000 total points
ID: 24108805
remove the trailing ) from this line:

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

765 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