SQL Syntax to Insert or Update

If you look at the bottom of this code there is a section that deals with either inserting or updating depending on whether a record exists or not. I am trying to learn to no longer use cursors in my code. How do you restructure this code to eliminate the cursor and is there a better way of handling the record exists condition when doing an insert or update?
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[rbsUpdateJobLinkerPTE]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.rbsUpdateJobLinkerPTE
END
GO

/****** Object:  StoredProcedure [dbo].[rbsUpdateJobLinkerPTE]    Script Date: 05/09/2010 18:54:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[rbsUpdateJobLinkerPTE]
(
@JOBNUMBER   char(15),
@BACHNUMB    char(15),
@COMPANYID   char(5),
@BCHSOURC    char(15),
@TRANSNMBR   char(21),
@MASTERTYPE  char(3),
@MASTERID    char(15),
@DOCAMNT     decimal(19,5),
@TRXTYPE     char(20),
@DSTSQNUM    int,
@CNTRLTYP    smallint,
@APTVCHNM    char(21),
@SPCLDIST    smallint,
@DOCNUMBR    char(21),
@DOCDATE     datetime,
@ACTINDX     int,
@CRDTAMNT    decimal(19,5),
@DEBITAMT    decimal(19,5),
@DISTTYPE    char(30),
@DISTTRXTYPE char(20),
@DISTSOURCE  char(50),
@DISTREF     char(128),
@TRXSORCE    char(13),
@DOCSOURCE   char(32)
)
AS

DECLARE @DOCTYPE char(20)
DECLARE @TYPEDIST char(30)
DECLARE @ACTNUMST char(64)

/* Delete any records that were removed from GP payables distribution */
DELETE [@COMPANYID].[dbo].[JOB_LINKER]
WHERE DSTSQNUM NOT IN (
SELECT DSTSQNUM FROM JOB_DISTSQNM
WHERE COMPANYID= @COMPANYID AND TRANSNMBR = @DOCNUMBR AND MASTERTYPE=@MASTERTYPE)
AND COMPANYID = @COMPANYID AND TRANSNMBR = @DOCNUMBR AND MASTERTYPE=@MASTERTYPE

/* Declare cursor to retrieve GL ACTNUMST */
DECLARE GetGLActNumstCurs CURSOR for
select ACTNUMST
FROM GL00105 
WHERE ACTINDX = @ACTINDX

/* Convert the transaction type numeric value to its character string equivalent */
SET @DOCTYPE = 
	CASE @TRXTYPE 
	WHEN 1 THEN 'Invoice'
    WHEN 2 THEN 'Finance Charge'
    WHEN 3 THEN 'Misc Charge'
    WHEN 4 THEN 'Return'
    WHEN 5 THEN 'Credit Memo'
    ELSE 'Unknown'
END

/* Convert the distribution type numeric value to its character string equivalent */
SET @TYPEDIST=
CASE @DISTTYPE
    WHEN 1 THEN 'CASH'
    WHEN 2 THEN 'PAY'
    WHEN 3 THEN 'AVAIL'
    WHEN 4 THEN 'TAKEN'
    WHEN 5 THEN 'FNCHG'
    WHEN 6 THEN 'PURCH'
    WHEN 7 THEN 'TRADE'
    WHEN 8 THEN 'MISC'
	WHEN 9 THEN 'FREIGHT'
    WHEN 10 THEN 'TAXES'
    WHEN 11 THEN 'WRITE'
    WHEN 12 THEN 'OTHER'
    WHEN 13 THEN 'GST'
    WHEN 14 THEN 'WH'
    WHEN 15 THEN 'UNIT'
    WHEN 16 THEN 'ROUND'
    ELSE 'Unknown'
END

/* Open the GL ACTNUMST cursor and get the account number string */
OPEN GetGLActNumstCurs

FETCH NEXT FROM GetGLActNumstCurs INTO @ACTNUMST

CLOSE GetGLActNumstCurs
DEALLOCATE GetGLActNumstCurs

/* Insert or update the distribution record as necessary */
IF NOT EXISTS (SELECT * FROM [@COMPANYID].[dbo].[JOB_LINKER] WHERE COMPANYID=@COMPANYID AND BCHSOURC=@BCHSOURC AND TRANSNMBR=@TRANSNMBR AND TRXTYPE=@DOCTYPE AND DSTSQNUM=@DSTSQNUM AND CNTRLTYP=@CNTRLTYP AND APTVCHNM=@APTVCHNM AND SPCLDIST=@SPCLDIST)
BEGIN
	/* INSERT THE VALUES INTO THE JOB LINKER TABLE FOR THOSE RECORDS THAT DO NOT EXIST */
	INSERT INTO [@COMPANYID].[dbo].[JOB_LINKER]
	(JOBNUMBER,BACHNUMB,COMPANYID,BCHSOURC,TRANSNMBR,MASTERTYPE,MASTERID,DOCAMNT,TRXTYPE,DSTSQNUM,CNTRLTYP,APTVCHNM,SPCLDIST,DOCNUMBR,DOCDATE,ACTNUMST,CRDTAMNT,DEBITAMT,DISTTYPE,DISTTRXTYPE,DISTSOURCE,DISTREF,TRXSORCE,DOCSOURCE)
	VALUES
	('',@BACHNUMB,@COMPANYID,@BCHSOURC,@TRANSNMBR,@MASTERTYPE,@MASTERID,@DOCAMNT,@DOCTYPE,@DSTSQNUM,@CNTRLTYP,@APTVCHNM,@SPCLDIST,@DOCNUMBR,@DOCDATE,@ACTNUMST,@CRDTAMNT,@DEBITAMT,@TYPEDIST,' ',@DISTSOURCE,@DISTREF,@TRXSORCE,@DOCSOURCE)
END
ELSE
BEGIN
	UPDATE [@COMPANYID].[dbo].[JOB_LINKER]
	SET BACHNUMB=@BACHNUMB,BCHSOURC=@BCHSOURC,TRANSNMBR=@TRANSNMBR,MASTERID=@MASTERID,DOCAMNT=@DOCAMNT,TRXTYPE=@DOCTYPE,DSTSQNUM=@DSTSQNUM,CNTRLTYP=@CNTRLTYP,APTVCHNM=@APTVCHNM,SPCLDIST=@SPCLDIST,DOCNUMBR=@DOCNUMBR,DOCDATE=@DOCDATE,ACTNUMST=@ACTNUMST,CRDTAMNT=@CRDTAMNT,DEBITAMT=@DEBITAMT,DISTTYPE=@TYPEDIST,DISTSOURCE=@DISTSOURCE,TRXSORCE=@TRXSORCE,DOCSOURCE=@DOCSOURCE
	WHERE COMPANYID= @COMPANYID AND BCHSOURC=@BCHSOURC AND TRANSNMBR=@TRANSNMBR AND TRXTYPE=@DOCTYPE AND DSTSQNUM=@DSTSQNUM AND CNTRLTYP=@CNTRLTYP AND APTVCHNM=@APTVCHNM AND SPCLDIST=@SPCLDIST 
END


-- grant execute on rbsUpdateJobLinkerPTE to DYNGRP

Open in new window

LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
Will the following always only give you one row?

select ACTNUMST FROM GL00105 WHERE ACTINDX = @ACTINDX

Based on the logic it is so then you can put it as a subselect instead:

INSERT INTO [@COMPANYID].[dbo].[JOB_LINKER]
      (JOBNUMBER,BACHNUMB,COMPANYID,BCHSOURC,TRANSNMBR,MASTERTYPE,MASTERID,DOCAMNT,TRXTYPE,DSTSQNUM,CNTRLTYP,APTVCHNM,SPCLDIST,DOCNUMBR,DOCDATE,ACTNUMST,CRDTAMNT,DEBITAMT,DISTTYPE,DISTTRXTYPE,DISTSOURCE,DISTREF,TRXSORCE,DOCSOURCE)
      VALUES
      ('',@BACHNUMB,@COMPANYID,@BCHSOURC,@TRANSNMBR,@MASTERTYPE,@MASTERID,@DOCAMNT,@DOCTYPE,@DSTSQNUM,@CNTRLTYP,@APTVCHNM,@SPCLDIST,@DOCNUMBR,@DOCDATE,  (select ACTNUMST FROM GL00105 WHERE ACTINDX = @ACTINDX),
@CRDTAMNT,@DEBITAMT,@TYPEDIST,' ',@DISTSOURCE,@DISTREF,@TRXSORCE,@DOCSOURCE)


update [@COMPANYID].[dbo].[JOB_LINKER]
SET      BACHNUMB=@BACHNUMB,BCHSOURC=@BCHSOURC,TRANSNMBR=@TRANSNMBR,MASTERID=@MASTERID,DOCAMNT=@DOCAMNT,TRXTYPE=@DOCTYPE,DSTSQNUM=@DSTSQNUM,CNTRLTYP=@CNTRLTYP,APTVCHNM=@APTVCHNM,SPCLDIST=@SPCLDIST,DOCNUMBR=@DOCNUMBR,DOCDATE=@DOCDATE,
 ACTNUMST=(select ACTNUMST FROM GL00105 WHERE ACTINDX =@ACTINDX),
 CRDTAMNT=@CRDTAMNT,DEBITAMT=@DEBITAMT,DISTTYPE=@TYPEDIST,DISTSOURCE=@DISTSOURCE,TRXSORCE=@TRXSORCE,DOCSOURCE=@DOCSOURCE
WHERE COMPANYID= @COMPANYID AND BCHSOURC=@BCHSOURC AND TRANSNMBR=@TRANSNMBR AND TRXTYPE=@DOCTYPE AND DSTSQNUM=@DSTSQNUM AND CNTRLTYP=@CNTRLTYP AND APTVCHNM=@APTVCHNM AND SPCLDIST=@SPCLDIST

An alternate insert statement would be:
INSERT INTO [@COMPANYID].[dbo].[JOB_LINKER]
      (JOBNUMBER,BACHNUMB,COMPANYID,BCHSOURC,TRANSNMBR,MASTERTYPE,MASTERID,DOCAMNT,TRXTYPE,DSTSQNUM,CNTRLTYP,APTVCHNM,SPCLDIST,DOCNUMBR,DOCDATE,ACTNUMST,CRDTAMNT,DEBITAMT,DISTTYPE,DISTTRXTYPE,DISTSOURCE,DISTREF,TRXSORCE,DOCSOURCE)
SELECT
      '',@BACHNUMB,@COMPANYID,@BCHSOURC,@TRANSNMBR,@MASTERTYPE,@MASTERID,@DOCAMNT,@DOCTYPE,@DSTSQNUM,@CNTRLTYP,@APTVCHNM,@SPCLDIST,@DOCNUMBR,@DOCDATE,       GL00105.ACTNUMST,
@CRDTAMNT,@DEBITAMT,@TYPEDIST,' ',@DISTSOURCE,@DISTREF,@TRXSORCE,@DOCSOURCE
FROM GL00105 WHERE ACTINDX = @ACTINDX
0
SharathData EngineerCommented:
I don't understand why did you have the cursor in your code. You are not executing any SQL queried between OPEN/CLOSE operations of CURSOR.
/* Open the GL ACTNUMST cursor and get the account number string */
OPEN GetGLActNumstCurs

FETCH NEXT FROM GetGLActNumstCurs INTO @ACTNUMST

CLOSE GetGLActNumstCurs
DEALLOCATE GetGLActNumstCurs

Open in new window

Coming to INSERT/UPDATE operation, checking with IF cluase is fine. Instead of *, you can use 1.
IF NOT EXISTS (SELECT 1 FROM [@COMPANYID].[dbo].[JOB_LINKER] WHERE COMPANYID=@COMPANYID AND BCHSOURC=@BCHSOURC AND TRANSNMBR=@TRANSNMBR AND TRXTYPE=@DOCTYPE AND DSTSQNUM=@DSTSQNUM AND CNTRLTYP=@CNTRLTYP AND APTVCHNM=@APTVCHNM AND SPCLDIST=@SPCLDIST)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johanntagleCommented:
/* Open the GL ACTNUMST cursor and get the account number string */
OPEN GetGLActNumstCurs

FETCH NEXT FROM GetGLActNumstCurs INTO @ACTNUMST

CLOSE GetGLActNumstCurs
DEALLOCATE GetGLActNumstCurs

Open in new window


Ah, based on the above code it may be possible that the select statement in the cursor may return more than one row, but you only use the first.  Then just update  the modified statements in my previous post to limit it to only one row (e.g. "limit 1" in mysql, "and rownum <=1" in Oracle, not sure what db you are using)
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

rwheeler23Author Commented:
In my case, this routine is called from code I inserted into an accounting program. This is MS SQL. In the accounting program is has a field called ACTINDX which is just an integer. What I need to do is to convert it into a human readable string equivalent(ACTNUMST). So ACTINDX 1 may be 1000 and 2 is 1010 and 3 is 1020 and so on and so on. There is a one to one relationship there. I was just trying to get that along with all the other parameters I sent down form the acounting program before I insert them into another table for further processing. So at the end records in my table may or may not already exist. This decision is not based on the ACTINDX value but on the uniqueness you see if the IF NOT EXIST statement. I like the idea of the SELECT 1. That should speed things up.

As usual I always get great tips from you folks. Thanks.
0
SharathData EngineerCommented:
>> om code I inserted into an accounting program. This is MS SQL. In the accounting program is has a field called ACTINDX which is just an integer. What I need to do is to convert it into a human readable string equivalent(ACTNUMST). So ACTINDX 1 may be 1000 and 2 is 1010 and 3 is 1020 and so on and so on.

Can you explain your requirement more with some sample data ans expected result?
0
johanntagleCommented:
So you're saying you use @ACTNUMST elsewhere beside the insert and update statements?  Then it is best that you don't use the subselects I described in my previous post.  But don't use a cursor since you are only expecting one result (but you already know that =) ).  I don't know if MS-SQL accepts this but in Oracle I would do:

select ACTNUMST INTO @ACTNUMST FROM GL00105 WHERE ACTINDX = @ACTINDX

Then you can retain your original insert and update statements.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.