[RESOLVED] Stored Procedure IF Statement Question

[techgnome]

First, lets set some ground work.... because SQL doesn't have an End if construct.... the IF statement only applies to the NEXT IMMEDIATE line that follows....If you surround several lines with a BEGIN END, then they get treated as a single statement block and will get processed together....
in your example:

     DECLARE @var1 int, @var2 int
      SET @var1 = 1
      SET @var2 = 2
      IF @var1 = @var2
        PRINT @var1
      PRINT @var2

Because Print @Var1 & Print @Var2 are not in the same block, only w@Var2 gets printed.... the IF only applies to the first plrint (since the two vars are different, the IF Fails - as it should).

Which brings us to example #2

     DECLARE @var1 int, @var2 int
      SET @var1 = 1
      SET @var2 = 2
      IF @var1 = @var2
        BEGIN
          PRINT @var1
          PRINT @var2
        END

Because the two prints are grouped by BEGIN END, they get treated as a block. Again, the values don't match, so the IF fails again, and nothing is printed.

OK, that covers the basics, now for your sp itself....
You got half of it right, but then seem to lose the lession....
Your first IF is OK.... and you've put everything in to a BEGIN END, so that's good, it'll only execute when the Action is REMOVEIMPORT.

It's your second IF where things go wrong. If the IF is successful, the next line, the DELETE will run, as will all remaining DELETES, folowed by the RETURN 0
If it FAILS, the first DELETE will be skipped, and it will start with the SECOND DELETE, run through the rest and RETURN 0.... then throw an error on the ELSE.... maybe... it might not since the RETURN is immediate....

What you need to do is surround the DELETES and the RETURN 0 with a BEGIN END. Like this:

ALTER PROCEDURE [dbo].[frmOpenImportRemoveItem]

@nvchrAction nvarchar(50) = '',
@ImportIDToDelete int = 0

AS
IF @nvchrAction = 'REMOVEIMPORT'

BEGIN

	-- verify no items were posted for this invoice. 
	 IF ((SELECT count(bitProcessed) 
			FROM tblInvoiceImportHistory 
			WHERE intCustomerInvoiceImportID  = @ImportIDToDelete) = 0)
           BEGIN
		-- If there are no posted items, then remove the import
		DELETE	tblInvoiceImportHistoryAddresses WHERE  intCustomerInvoiceImportID = @ImportIDToDelete
		DELETE	tblInvoiceImportHistoryAmounts  WHERE intCustomerInvoiceImportID  = @ImportIDToDelete
		DELETE	tblInvoiceImportHistory WHERE  intCustomerInvoiceImportID  = @ImportIDToDelete
		DELETE	tblInvoiceImportParams WHERE  intCustomerInvoiceImportParamID  = @ImportIDToDelete
		-- indicate successful
		RETURN 0
             END
	ELSE
		-- otherwise indicate not successful
		RETURN 1	 -- Because this is a single line, no BEGIN/END is needed.... I usually do so anyways jsut for clarity.
	END

END

-tg