Insert or Update Stored Procedure

Question

Insert or Update Stored Procedure

er sql

SSC Eights!

Points: 943
More actions
February 15, 2005 at 4:42 pm

#163768

Hi, I´m new with stored procedure, can anyone help me with this.
I need to insert or update records on a table and It depends if the record exist or not, I mean, If the record exists I need to update only 2 columns of my table, if not exists I need to insert the new record.

Thanks a lot

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Are you using an external app (DTS, VB, etc...) which determines if a record exists?

One option is to create a seperate stored procedure for each action. Then create another procedure which determines if the record exists and calls the appropriate procedure to perfrom the required action.

--------------------
Colt 45 - the original point and click interface

er sql SSC Eights! Points: 943 More actions · Answer 2

I need to run the stored procedure in Sql Server and It's going to be called by Oracle, Oracle has the data.

I wrote these:

CREATE PROCEDURE SP_SIAB_INS_UPD (

@NO_BIEN AS INT,

@DESCRIPCION AS VARCHAR(1250),

@CANTIDAD as int,

@DEL_ADMON AS VARCHAR(80),

@DEL_RECIBE AS VARCHAR(80),

@DES_TIPO AS VARCHAR(70),

@DES_SUBTIPO AS VARCHAR(70),

@DES_SSUBTIPO AS VARCHAR (70),

@DES_SSSUBTIPO AS VARCHAR (70),

@NO_ALMACEN AS INT,

@NO_EXPEDIENTE AS INT,

@ESTATUS AS VARCHAR (3),

@FECHA_DONACION AS DATETIME,

@CLAVE_ACTA AS VARCHAR (60)

&nbsp

AS

BEGIN

DECLARE @SEL_NOBIEN INT

SELECT @SEL_NOBIEN = (SELECT NO_BIEN FROM TMP_SIAB_DONACION)

IF @SEL_NOBIEN = @NOBIEN

UPDATE TMP_SIAB_DONMACION SET

CANTIDAD = @CANTIDAD,

ESTATUS = @ESTATUS

WHERE @SEL_NOBIEN = @NOBIEN

RETURN @NO_BIEN

END

ELSE

INSERT INTO TMP_SIAB_DONACION

(NO_BIEN,

DESCRIPCION,

CANTIDAD,

DEL_ADMON,

DEL_RECIBE,

DES_TIPO,

DES_SUBTIPO,

DES_SSUBTIPO,

DES_SSSUBTIPO,

NO_ALMACEN,

NO_EXPEDIENTE,

ESTATUS,

FECHA_DONACION,

CLAVE_ACTA)

VALUES

(@NO_BIEN,

@DESCRIPCION,

@CANTIDAD,

@DEL_ADMON,

@DEL_RECIBE,

@DES_TIPO,

@DES_SUBTIPO,

@DES_SSUBTIPO,

@DES_SSSUBTIPO,

@NO_ALMACEN,

@NO_EXPEDIENTE,

@ESTATUS,

@FECHA_DONACION,

@CLAVE_ACTA)

GO

philcart SSC-Forever Points: 47794 More actions · Answer 3

"I need to run the stored procedure in Sql Server and It's going to be called by Oracle, Oracle has the data."

This sort of information would have been helpful in your original post

So you want to execute and SQL Server stored procedure from within Oracle? Is the Oracle database on Windows or *nix?

--------------------
Colt 45 - the original point and click interface

er sql SSC Eights! Points: 943 More actions · Answer 4

er sql

SSC Eights!

Points: 943

February 16, 2005 at 8:26 am

#542554

The Oracle Database is on Windows.

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 5

Is the issue how to execute the proc from Oracle, or getting the proc itself right?

Quick pseudo-code for ins/upd proc:

create procedure InsUpd
(@pkey_field1
,@pkey_field2
,@insert_only_field1
,@insert_only_field2
,@update_field1
,@update_field2
)
AS

IF (EXISTS (SELECT * from table
             WHERE pkey_field1 = @pkey_field1
               AND pkey_field2 = @pkey_field2))
BEGIN
  UPDATE table
     SET update_field1 = @update_field1
        ,update_field2 = @update_field2
   WHERE pkey_field1 = @pkey_field1
     AND pkey_field2 = @pkey_field2
END
ELSE
BEGIN
  INSERT INTO TABLE
        (pkey_field1
        ,pkey_field2
        ,insert_only_field1
        ,insert_only_field2
        ,update_field1
        ,update_field2)
  VALUES
        (@pkey_field1
        ,@pkey_field2
        ,@insert_only_field1
        ,@insert_only_field2
        ,@update_field1
        ,@update_field2)
END
GO

R David Francis