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 DYNGRPASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
/* Open the GL ACTNUMST cursor and get the account number string */
OPEN GetGLActNumstCurs
FETCH NEXT FROM GetGLActNumstCurs INTO @ACTNUMST
CLOSE GetGLActNumstCurs
DEALLOCATE GetGLActNumstCursAh, 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)
ASKER
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.
As usual I always get great tips from you folks. Thanks.
>> 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?
Can you explain your requirement more with some sample data ans expected result?
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.
select ACTNUMST INTO @ACTNUMST FROM GL00105 WHERE ACTINDX = @ACTINDX
Then you can retain your original insert and update statements.
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_LI
(JOBNUMBER,BACHNUMB,COMPAN
VALUES
('',@BACHNUMB,@COMPANYID,@
@CRDTAMNT,@DEBITAMT,@TYPED
update [@COMPANYID].[dbo].[JOB_LI
SET BACHNUMB=@BACHNUMB,BCHSOUR
ACTNUMST=(select ACTNUMST FROM GL00105 WHERE ACTINDX =@ACTINDX),
CRDTAMNT=@CRDTAMNT,DEBITAM
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_LI
(JOBNUMBER,BACHNUMB,COMPAN
SELECT
'',@BACHNUMB,@COMPANYID,@B
@CRDTAMNT,@DEBITAMT,@TYPED
FROM GL00105 WHERE ACTINDX = @ACTINDX