Please Help Me Correcting Stored Procedure With If Statement . Getting Error .
Create Procedure ConsignmentUpdate (
         @EntryNo bigint , 
         @ccode bigint , 
         @CustomerName nvarchar(250),
         @Shipper nvarchar(50),
         @Barcode nvarchar(50) ,
         @JobNo nvarchar(100),
         @Packages bigint , 
         @BL nvarchar(100),
         @EntryDate smalldatetime , 
         @DeliveryDate smalldatetime,
         @ContainerNo nvarchar(50),
         @OffLoading  nvarchar(50) , 
         @Destination nvarchar(50) , 
         @ConsignmentNo bigint )
As 
      
       Declare @FBarcode as nvarchar 
       Declare @Delivery as bigint
       Select @FBarcode = Barcode , @Delivery = EntryNo From ConsignmentDelivery Where ConsignmentNo = @ConsignmentNo
       If (@FBarcode = null )
          (Begin 
          End) 
        Else 
                 Update ConsignmentDelivery 
                  Set  ccode = @ccode , 
                  CustomerName = @CustomerName , 
                  Shipper = @Shipper , 
                  Barcode = @Barcode ,
                  JobNo = @JobNo , 
                  Packages = @Packages , 
                  BL = @BL , 
                  EntryDate = @EntryDate , 
                  DeliveryDate = @DeliveryDate ,
                  ContainerNo = @ContainerNo , 
                  OffLoading = @OffLoading , 
                  Destination = @Destination 
                  Where EntryNo = @Delivery  
		End 
         Begin
	              Update Consignment 
                  Set ccode = @ccode , 
                  CustomerName = @CustomerName , 
                  Shipper = @Shipper , 
                  Barcode = @Barcode ,
                  JobNo = @JobNo , 
                  Packages = @Packages , 
                  BL = @BL , 
                  EntryDate = @EntryDate , 
                  DeliveryDate = @DeliveryDate ,
                  ContainerNo = @ContainerNo , 
                  OffLoading = @OffLoading , 
                  Destination = @Destination
                  Where EntryNo = @EntryNo
           End
September 4th, 2015 10:37am

Looks like you forgot the "Begin" after the first "Else"...

 Else 
BEGIN
                 Update ConsignmentDelivery 
                  Set  ccode = @ccode ,

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 10:42am
       If (@FBarcode = null )
          (Begin 
          End)
September 4th, 2015 10:50am

Try the following modifications:

Declare @FBarcode as nvarchar(50) ;
       Declare @Delivery as bigint
       Select @FBarcode = Barcode , @Delivery = EntryNo From ConsignmentDelivery Where ConsignmentNo = @ConsignmentNo
       If @Delivery is NOT NULL
                 Update ConsignmentDelivery 
                  Set  ccode = @ccode , 
                  CustomerName = @CustomerName , 
                  Shipper = @Shipper , 
                  Barcode = @Barcode ,
                  JobNo = @JobNo , 
                  Packages = @Packages , 
                  BL = @BL , 
                  EntryDate = @EntryDate , 
                  DeliveryDate = @DeliveryDate ,
                  ContainerNo = @ContainerNo , 
                  OffLoading = @OffLoading , 
                  Destination = @Destination 
                  Where EntryNo = @Delivery  
	else

	          Update Consignment 
                  Set ccode = @ccode , 
                  CustomerName = @CustomerName , 
                  Shipper = @Shipper , 
                  Barcode = @Barcode ,
                  JobNo = @JobNo , 
                  Packages = @Packages , 
                  BL = @BL , 
                  EntryDate = @EntryDate , 
                  DeliveryDate = @DeliveryDate ,
                  ContainerNo = @ContainerNo , 
                  OffLoading = @OffLoading , 
                  Destination = @Destination
                  Where EntryNo = @EntryNo

It is not clear from your code where did you want to use new variable @FBarcode, may be in the first block if it's not null.

Actually, the whole intent of the code is not clear and not clear what did you want to do at all. Can you explain in English what this procedure needs to do? May be you wanted to insert new entry if the @EntryNo is not found and update if found? In this case we would be better using a single MERGE stat

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 11:02am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics