Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Check for existing data and then insert if not existing

Posted on 2009-04-09
1
Medium Priority
?
682 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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 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