So maybe I missing something basic......
What I am trying to accomplish is this...
If TXNID = MODID, great.... if not, INSERT A new record, (based upon
an existing template record) .... and then return the new record as the query.
''''''''''''''''''''''''''''''''''''''''''''''
CREATE PROCEDURE [dbo].[TXNOCR_CHK_ADD]
@XOIF nvarchar(5) ,
@MODID INT,
@Pfx nvarchar(1)='Z'
As
Declare @wRecNum nvarchar(9);
Declare @wAltRec nvarchar(5);
Declare @wAltKey nvarchar(1)='Z';
Declare @wModID int=@Modid;
Declare @wCOID nvarchar(5);
Declare @wsID as nvarchar(2)= cast(@Modid as int)
Declare @wSfx as nvarchar(6)='A1A1A1';
Begin
if exists(Select * From TXNOCR Where TXNID=@MODID AND XOIF=@XOIF)
Select * From TXNOCR Where TXNID=@MODID AND XOIF=@XOIF
ELSE
-- Ok, Lets see if we have a default record, from default of ZZZZ9
BEGIN
if exists(Select * From TXNOCR Where TXNID=@MODID AND XOIF='ZZZZ9')
Select @wRecNum=m.RecNum, @wAltRec=m.AltRec, @wAltKey=m.RecKeyID From MODOCR m Where ModID=@MODID AND XOIF='ZZZZ9';
Insert ModOcr(COID,Modid,REcnum,DfltUSe,AltRec) Values(@COID,@wModID,@wREcnum,'---',@wAltKey');
ELSE
--- Cannot even find default, so use other parms and insert
--- what we have to
BEGIN
Insert TXNOcr(XOIF,Modid,REcnum,DfltUSe,REcKeyID,AltRec)
Values(@XOIF,@wModID,@wAltKey + @wsID + @wsfx,'---',@wAltKey);
END
END
-- Regardless of how we got here, now reselect
Select * From TXNOCR Where TXNID=@MODID AND XOIF=@XOIF;
END
'===============
When I go to add, it says I have syntax errors next to ELSE.
I thought that I could have a SELECT .... and then an INSERT nestled together...