[RESOLVED] Stored Procedure IF Statement Question

[rjbudz]

I seem to be missing something here. This is from a SQL book:

======
In the following snippet, we have two similar stored procedures. The difference is in the use of the BEGIN … END code block. Executing the first one will execute the second PRINT statement, as the IF statement will take only the first line as part of the decision:

    CREATE PROCEDURE ut_NoBeginEnd
    AS
    BEGIN
      DECLARE @var1 int, @var2 int
      SET @var1 = 1
      SET @var2 = 2
      IF @var1 = @var2
        PRINT @var1
      PRINT @var2
    END

Compare it with the following snippet. Here, neither of the PRINT statements will be executed:

    CREATE PROCEDURE ut_BeginEnd
    AS
    BEGIN

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

======
Now, in the first example, why does the first PRINT statement not run?
In the second, why don't either of them run?

I need to create an IF statement based on a SELECT Statement 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)

		-- 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
	ELSE
		-- otherwise indicate not successful
		RETURN 1	
	END

END

It seems simple enough to me. If there are records found from the SELECT statement then I don't want to do the DELETE statements, but need to let the calling procedure know this was a problem, but can't convince SQL Server of all this.

I'm considering returning 1 if @ImportIDToDelete int = 0, but one step at a time.

So, what am I missing?

Thanks you!

[Greyskull]

Well because var 1 and var 2 are not equal....

[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

[rjbudz]

Yep.

That makes perfect sense. I didn't look closely enough at the examples to see that var1 and var2 are not equal. Guess I just expected the answer to jump out at me.

There seems to be one too many END statements in your revision. My guess is the one after the Else is not required (or would be valid with a BEGIN statement immediately after the ELSE, correct?

Thanks very much for your help!

[techgnome]

Correct... that came from what you had originally posted and I missed it.... but, yes, it's not needed.

-tg

[rjbudz]

Thanks!