SQLTeam.com | Weblogs | Forums

Missing ELSE - IF syntax, Help


#1

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...


#2

If it's more than one statement, you need another BEGIN ... END. And remove the single quote after @wAltkey:

...
BEGIN
if exists(Select * From TXNOCR Where TXNID=@MODID AND XOIF='ZZZZ9')
begin
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);
end
ELSE
...


#3

Thanks... I knew it had to be something simple...
I'll get that try.

Thx again


#4

That's because of this rogue single-quote mark:

Insert ModOcr(COID,Modid,REcnum,DfltUSe,AltRec) 
    Values(@COID,@wModID,@wREcnum,'---',@wAltKey');
                                             ---^---
ELSE

but as Scott has said, the IF / BEGIN / END / ELSE flow is not right.

Also:

Declare @wsID as nvarchar(2)= cast(@Modid as int)

should presumably be?:

Declare @wsID as nvarchar(2)= cast(@Modid as NVarchar(2))
if exists(Select * From TXNOCR Where TXNID=@MODID AND XOIF=@XOIF) 
	Select * From TXNOCR Where TXNID=@MODID AND XOIF=@XOIF

you should not use SELECT * in the second statement (its fine in the EXISTS), you should list the names of all the columns (but only the ones that teh APP / Client needs) explicitly.

Otherwise, in future, any columns added to the table will be retrieved by SELECT * and if the Client-end doesn't use them they will just consume bandwidth unnecessarily.

I was called in by a new Client to figure out why the performance of an APP had taken a nose dive ... turned out that the in-house developers had used SELECT * everywhere, and then one day the Call Centre said "Could we have a NOTES field on the Customer table?" ... so that was added, Call Centre were thrilled and added heaps of stuff about each Customer ... and then every SELECT * pulled that, now huge, text block on EVERY query and it completely killed the performance as on all queries, except the Call Centre Notes screen, it was not needed. Took them a month solid to change (and test) all the code to remove all the SELECT * statements.

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');

@COID is undefined?

note that there is "opportunity" for another user to add the TXNOCR record AFTER your EXISTS test doesn't find it and BEFORE your INSERT runs :frowning: (so you may need some locking / serialisation hints)

Personally I would not do a SELECT into @Variables and then an INSERT ... VALUES, instead I would do:

	IF EXISTS
	(
		SELECT	*
		FROM	TXNOCR
		Where	TXNID=@MODID
			AND XOIF='ZZZZ9'
	)
	BEGIN 
		INSERT INTO ModOcr
		(
			COID,Modid,REcnum,DfltUSe,AltRec
		)
		SELECT	@COID		-- UNDEFINED
			, @wModID
			, m.RecNum	-- @wREcnum
			, '---'
			, m.AltRec	-- @wAltKey
		FROM	MODOCR AS m
		WHERE	ModID=@MODID
			AND XOIF='ZZZZ9'
	END
	ELSE
	BEGIN 
		INSERT TXNOcr(XOIF,Modid,REcnum,DfltUSe,REcKeyID,AltRec) 
		VALUES(@XOIF,@wModID,@wAltKey + @wsID + @wsfx,'---',@wAltKey);
	END

The second one could be a INSERT + SELECT if you prefer - for example, instead of the DECLARES at the top you could just String-Manipulate / CAST etc. the values in a SELECT here, which were then inserted. Its just a personal-preference choice though.

I recommend that you prefix ALL table names with the schema - so instead of "FROM TXNOCR" probably "FROM dbo.TXNOCR" (unless the object is, indeed, deliberately specific to the current user)