IF THEN ELSE Problems

[bminaeff]

Hey all,

I am new to doing if then else's in SQL and I am having a tough time. The Errors I am receiving are the following:

Msg 156, Level 15, State 1, Procedure Lane_Destination_R2, Line 72
Incorrect syntax near the keyword 'THEN'.
Msg 102, Level 15, State 1, Procedure Lane_Destination_R2, Line 77
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Procedure Lane_Destination_R2, Line 82
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure Lane_Destination_R2, Line 88
Incorrect syntax near the keyword 'END'.

I am guessing this is totally wrong, but it seems similar to the if then else clauses I find online. Any idea on how to clean this all up?

Thanks
-Bill

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Lane_Destination_R2] 
	-- Add the parameters for the stored procedure here
	(@UCC_bc nvarchar(50) = 99, 

@DC_bc nvarchar(50) = 99,

@Zip_bc nvarchar(50) = 99,

@CustOut nvarchar(50) OUTPUT,

@Destination nvarchar(50) OUTPUT,

@Lane char(1) OUTPUT,

@ReasonCode nvarchar(50) OUTPUT,

@UniqueID nvarchar (50) OUTPUT)

AS
BEGIN
DECLARE @ContainerNum nvarchar(50)

DECLARE @DCID nvarchar(50)

DECLARE @Customer nvarchar(50)

DECLARE @ZipNum nvarchar(50)

DECLARE @Container nvarchar(50)

DECLARE @CustomerID nvarchar(50)

DECLARE @Retailer nvarchar(50)

DECLARE @RetailerID nvarchar(50)

DECLARE @UCCMode char(1)

DECLARE @DCIDMode char(1)

DECLARE @CustMode char(1)

DECLARE @ContMode char(1)

DECLARE @RetMode char(1)

DECLARE @Customer1 nvarchar(50)

DECLARE @Destination1 nvarchar(50)

DECLARE @Status char(1)

DECLARE @Reject char(1)

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT @Reject = LaneMap FROM dbo.LaneMappingTable1 WHERE Customer = 'Reject' AND Destination = 'Reject'
	SELECT @Retailer = Retailer FROM dbo.Details WHERE CartonID = @UCC_BC
	SELECT @Customer = Customer FROM dbo.Details WHERE CartonID = @UCC_BC
	SELECT @ContainerNum = ContNum FROM dbo.Details WHERE CartonID = @UCC_BC

     -- Insert statements for procedure here
	SELECT * FROM dbo.SortRules WHERE (UCCMode = 'S' AND UCC = @UCC_bc)

	IF DCID = @DCID AND DCIDMode = 'V' THEN
	SELECT @Lane = LaneMap, @ReasonCode = '00' from dbo.LaneMappingTable1 where Customer = @CustOut and Destination = @Destination
	SELECT @Lane = @Reject, @UniqueID = UniqueID from dbo.SortRules where UCC = @UCC_bc
	ELSEIF @DCIDMode = 'V' THEN
	SELECT @Lane = LaneMap, @ReasonCode = '02' from dbo.LaneMappingTable1 where Customer = @CustOut and Destination = @Destination
	SELECT @Lane = @Reject, @UniqueID = UniqueID from dbo.SortRules where UCC = @UCC_bc
	ELSE
	SELECT @Lane = LaneMap, @ReasonCode = '01' from dbo.LaneMappingTable1 where Customer = @CustOut and Destination = @Destination
	SELECT @Lane = @Reject, @UniqueID = UniqueID from dbo.SortRules where UCC = @UCC_bc
	END IF

END

 

[gmmastros]

Do not use [blue]THEN[/blue] or [blue]END IF[/blue].

Also, when there are multiple statements between If and else (or following the else), you need to use Begin/End.
Ex:

    IF DCID = @DCID AND DCIDMode = 'V'
      Begin
        SELECT @Lane = LaneMap, @ReasonCode = '00' 
        from   dbo.LaneMappingTable1 
        where  Customer = @CustOut 
               and Destination = @Destination

        SELECT @Lane = @Reject, @UniqueID = UniqueID 
        from   dbo.SortRules 
        where  UCC = @UCC_bc
      End 
    ELSE IF @DCIDMode = 'V'
      Begin
        SELECT @Lane = LaneMap, @ReasonCode = '02' 
        from   dbo.LaneMappingTable1 
        where  Customer = @CustOut 
               and Destination = @Destination
 
        SELECT @Lane = @Reject, @UniqueID = UniqueID 
        from   dbo.SortRules 
        where  UCC = @UCC_bc
      End
    ELSE
      Begin
        SELECT @Lane = LaneMap, @ReasonCode = '01' 
        from   dbo.LaneMappingTable1 
        where  Customer = @CustOut 
               and Destination = @Destination
 
        SELECT @Lane = @Reject, @UniqueID = UniqueID 
        from   dbo.SortRules 
        where  UCC = @UCC_bc
      End

That, at least, is the correct structure for using If/Else If/Else.

Make sense?

You have another problem that you haven't yet discovered though. This:

[tt][blue]IF DCID = @DCID AND DCIDMode = 'V'[/blue][/tt] is not valid. I cannot tell, from reading your code, what you are trying to accomplish or where the comparison values are coming from.

For example, @DCID is declared as a local variable, but does not appear to be getting set anywhere, so by the time it reaches this part of the code, it's value is NULL, which wouldn't compare to anything. So, the lines following the first IF would not ever get executed.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
Doug needs a new pair of shoes, please click an ad link.

 

[gmmastros]

While we're at it....

This:
[tt][blue] SELECT @Retailer = Retailer FROM dbo.Details WHERE CartonID = @UCC_BC
SELECT @Customer = Customer FROM dbo.Details WHERE CartonID = @UCC_BC
SELECT @ContainerNum = ContNum FROM dbo.Details WHERE CartonID = @UCC_BC[/blue][/tt]

Can be simplified to this....

    SELECT @Retailer = Retailer,
           @Customer = Customer,
           @ContainerNum = ContNum 
    FROM   dbo.Details 
    WHERE  CartonID = @UCC_BC

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
Doug needs a new pair of shoes, please click an ad link.

 

[bminaeff]

George,

I am not much of a SQL guy, so most of what I am doing is probably wrong. I just told my SQL guy what I can give and what I need out and I only know what I have doesnt work. Anyway, I should have DCID as DC_bc. I am giving the SP three values that come from a barcode scanner through a plc. The three values are UCC_bc, DC_bc, and ZIP_bc. The ZIP_bc is useless for now. The UCC_bc is a unique identifier and is what I search by. I have to search my "Details" table to get some other information about it (the customer, retailer, and container) but this should not be used yet either, so I can sort of ignore it for now. I then have to search the "SortRules" table to find a UCC that matches and has a UCC_mode = 'S'. The SortRules table also has a field for DCID and DCIDMode. If DCIDMode = 'V' then I have to compare what is returned for that UCC with what I sent in (DC_bc). IF they match then I have to get a Lane from another table based on two other paramaters in the SortRules table. If they dont match I have to return back a Lane based on a constant.

 

[bminaeff]

George,

Thanks for the help. This seems to be working now. Its actually not that hard once you get the syntax down.

 

[ESquared]

This first SQL statement:

        SELECT @Lane = LaneMap, @ReasonCode = '00' 
        from   dbo.LaneMappingTable1 
        where  Customer = @CustOut 
               and Destination = @Destination

Does nothing at all with rows from the table except set @Reasoncode if there's at least one matching the where clause. So convert that to an EXISTS. But you're also setting @Lane once here, then setting @Lane again in the next statement:

        SELECT @Lane = @Reject, @UniqueID = UniqueID 
        from   dbo.SortRules 
        where  UCC = @UCC_bc

Are you sure that's what you want?

Also, you cannot reference columns outside of a query:

    IF DCID = @DCID AND DCIDMode = 'V'

DCID and DCIDMode here are going to give errors because of this.

I can't give you working code because of the problems, but last, look:

SELECT
   @ReasonCode = 
   CASE
      WHEN DCID = @DCID AND DCIDMode = 'V' THEN '00'
      WHEN DCIDMode = 'V' THEN '02'
      ELSE '01'
   END
from   dbo.LaneMappingTable1 
where  Customer = @CustOut 
       and Destination = @Destination

SELECT @Lane = @Reject, @UniqueID = UniqueID 
from   dbo.SortRules 
where  UCC = @UCC_bc

To use the EXISTS I talked about:

IF EXISTS (
   select *
   from   dbo.LaneMappingTable1 
   where  Customer = @CustOut 
          and Destination = @Destination
)
SET
   @ReasonCode = 
   CASE
      WHEN DCID = @DCID AND DCIDMode = 'V' THEN '00'
      WHEN DCIDMode = 'V' THEN '02'
      ELSE '01'
   END

EXISTS makes the query in the parentheses stop when one row is found, and no columns are returned (despite the *).