asked on

SQL Stored Procedure: Insert / Update based on certain criteria

Hello:

I have a table called PreferenciasCliente with the following fields.
      [CodigoCliente] [char] (10) COLLATE Modern_Spanish_CI_AS NULL ,
      [IdiomaCAT] [bit] NULL ,
      [IdiomaCAST] [bit] NULL ,
      [IdiomaFR] [bit] NULL ,
      [IdiomaENG] [bit] NULL ,
      [PaletUE] [bit] NULL ,
      [PaletUEInt] [bit] NULL ,
      [MasterColor] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
      [Impresion] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
      [Etiquetas] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
      [ContEtiq] [text] COLLATE Modern_Spanish_CI_AS NULL ,
      [EspecEst] [text] COLLATE Modern_Spanish_CI_AS NULL

WHat I then have is two processes that interact with a single table. Right now, one deletes a record if [CodigoCliente] exists, if not it creates a new record. If it already exists, it gets overwritten. I now have another process which should update the following 4 fields:
[Impresion] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
      [Etiquetas] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
      [ContEtiq] [text] COLLATE Modern_Spanish_CI_AS NULL ,
      [EspecEst] [text] COLLATE Modern_Spanish_CI_AS NULL
...BUt I can't delete and add now that I have 2 actions. What I have to do is use a stored procedure to first see if the [CodigoCliente] exists, if it does, it should do an update for the appropriate fields; if not, it should add a new record to the table. How can I do this?

thanks,

Joseph

SOLUTION

Aneesh

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

Pratima

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Aneesh

pratima_mcs,

>select @cnt = count(*) from PreferenciasCliente with(nolock)
>where CodigoCliente =@CodigoCliente

Eventhough this works, it will have some performance overheads. check my code

Aneesh R.

SOLUTION

Mark Wills

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Mark Wills

Do the update first so you are not updating the stuff just inserted. Same kind of performance considerations Aneesh mentioned before.

CEGE

ASKER

Hello:

Thanks for the info. I tried this, but I am getting an error in the last line, which says "Line 36: Incorrect syntax near '@CodigoCliente'."
This is what I have:
alter PROCEDURE dbo.update_PrefCliente
@CodigoCliente char (10),
@IdiomaCAT bit,
@IdiomaCAST bit,
@IdiomaFR bit,
@IdiomaENG bit,
@PaletUE bit,
@PaletUEInt bit,
@MasterColor varchar (50) ,
@Impresion varchar (50) ,
@Etiquetas varchar (50) ,
@ContEtiq text ,
@EspecEst text
AS
BEGIN
declare @cnt as int
select @cnt = count(*) from PreferenciasCliente with(nolock)
where CodigoCliente =@CodigoCliente

if ( @cnt = 0 )
begin
-- Add Insert statement here
Insert PreferenciasCliente
select * from PreferenciasClienteAP
where not exists (select * from PreferenciasCliente where CodigoCliente = @CodigoCliente)

end
else
begin
Update PreferenciasCliente
Set
Impresion = @Impresion,
Etiquetas = @Etiquetas,
ContEtiq= @ContEtiq,
EspecEst =@EspecEst
where CodigoCliente = @CodigoCliente

Mark Wills

you need an END after it... Maybe get rid of the begin just before the update as well - need to have an END for each begin...

Mark Wills

hang on... need to look closely...

the not exists need to be comparing the supplying table with the destination table.
the @codigoCliente is used to select the supplying table.

Check the indentations - think there is a begin higher up...

   if ( @cnt = 0 )
   begin
              -- Add Insert statement here
      Insert PreferenciasCliente
      select * from PreferenciasClienteAP
      where not exists (select * from PreferenciasCliente P where PreferenciasClienteAP.CodigoCliente = P.CodigoCliente)
      and CodigoCliente = @CodigoCliente
   end
   else
   begin
      Update PreferenciasCliente
      Set  
         Impresion = @Impresion, 
         Etiquetas = @Etiquetas,
         ContEtiq= @ContEtiq,
         EspecEst =@EspecEst
      where CodigoCliente = @CodigoCliente
   end
end

Open in new window

Aneesh

>select @cnt = count(*) from PreferenciasCliente with(nolock)
where CodigoCliente =@CodigoCliente

The above one is an overhead, dont do that, pls have a look at my post

CEGE

ASKER

Thanks. In the end, this is working for me.

@Impresion varchar (50),
@Etiquetas varchar (50),
@ContEtiq varchar (50),
@EspecEst varchar (50) AS
DECLARE @TOTAL INT
SELECT @TOTAL= COUNT( @CODIGOCLIENTE) FROM
PREFERENCIASCLIENTE WHERE @CODIGOCLIENTE=CODIGOCLIENTE
IF @TOTAL=0
INSERT INTO PREFERENCIASCLIENTE (CodigoCliente,IdiomaCAT, IdiomaCAST,
IdiomaFR, IdiomaENG, PaletUE,PaletUEint, MasterColor,
Impresion, Etiquetas,ContEtiq, EspecEst)
VALUES (@CodigoCliente, @IdiomaCAT, @IdiomaCAST, @IdiomaFR,@IdiomaENG,
@PaletUE, @PaletUEint, @MasterColor, @Impresion, @Etiquetas, @ContEtiq, @EspecEst)
ELSE UPDATE PREFERENCIASCLIENTE
SET PaletUE=@PaletUE, Etiquetas=@Etiquetas, ContEtiq=@ContEtiq, EspecEst=@EspecEst
where codigocliente=@codigocliente

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
cheers,

joseph

CEGE

ASKER

thanks.

CEGE

ASKER

there was a mistake...
ALTER PROCEDURE UPDATE_PREFER
@CodigoCliente char (10),
@IdiomaCAT bit,
@IdiomaCAST bit,
@IdiomaFR bit,
@IdiomaENG bit,
@PaletUE bit,
@PaletUEint bit,
@MasterColor varchar (50),
@Impresion varchar (50),
@Etiquetas varchar (50),
@ContEtiq varchar (50),
@EspecEst varchar (50) AS
DECLARE @TOTAL INT
SELECT @TOTAL= COUNT( @CODIGOCLIENTE) FROM
PREFERENCIASCLIENTE WHERE @CODIGOCLIENTE=CODIGOCLIENTE
IF @TOTAL=0
INSERT INTO PREFERENCIASCLIENTE (CodigoCliente,IdiomaCAT, IdiomaCAST,
IdiomaFR, IdiomaENG, PaletUE,PaletUEint, MasterColor,
Impresion, Etiquetas,ContEtiq, EspecEst)
VALUES (@CodigoCliente, @IdiomaCAT, @IdiomaCAST, @IdiomaFR,@IdiomaENG,
@PaletUE, @PaletUEint, @MasterColor, @Impresion, @Etiquetas, @ContEtiq, @EspecEst)
ELSE UPDATE PREFERENCIASCLIENTE
SET PaletUE=@PaletUE, Etiquetas=@Etiquetas, ContEtiq=@ContEtiq, EspecEst=@EspecEst
where codigocliente=@codigocliente

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I just wanted it to be complete.