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
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
Microsoft SQL Server 2005DatabasesMicrosoft SQL Server
Last Comment
CEGE
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Do the update first so you are not updating the stuff just inserted. Same kind of performance considerations Aneesh mentioned before.
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
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
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...
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...
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
>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
where CodigoCliente =@CodigoCliente
The above one is an overhead, dont do that, pls have a look at my post
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=CODIGOCLIEN
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=@codigoclien
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
cheers,
joseph
@Impresion varchar (50),
@Etiquetas varchar (50),
@ContEtiq varchar (50),
@EspecEst varchar (50) AS
DECLARE @TOTAL INT
SELECT @TOTAL= COUNT( @CODIGOCLIENTE) FROM
PREFERENCIASCLIENTE WHERE @CODIGOCLIENTE=CODIGOCLIEN
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=@codigoclien
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
cheers,
joseph
ASKER
thanks.
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=CODIGOCLIEN
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=@codigoclien
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I just wanted it to be complete.
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=CODIGOCLIEN
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=@codigoclien
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I just wanted it to be complete.
>select @cnt = count(*) from PreferenciasCliente with(nolock)
>where CodigoCliente =@CodigoCliente
Eventhough this works, it will have some performance overheads. check my code
Aneesh R.