New SQL rowcount question 1

[bminaeff]

Hey all,

I have this Stored Procedure that works well but for some reason the first @@RowCount does not seem to work. Instead of giving me @Reject, '03' , and '000' it gives me all nulls when I search for a UCC_bc that does not exist in UCC. The other RowCounts work fine, but I can not see what the difference is, but I dont know much about SQL so.

Thanks
-Bill

-- 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, @Customer = Customer, @ContainerNum = ContNum FROM dbo.Details WHERE CartonID = @UCC_BC
     -- Insert statements for procedure here
	SELECT @DCID = DCID, @DCIDMode = DCIDMode FROM dbo.SortRules WHERE (UCCMode = 'S' AND UCC = @UCC_bc)
	If @@RowCount = 0 
	Begin
		SELECT @Lane = @Reject, @ReasonCode = '03', @UniqueID = '000' FROM dbo.SortRules WHERE (UCC = @UCC_bc)
	End
	Else	
	IF @DC_bc = @DCID AND @DCIDMode = 'V'
      Begin
		SELECT @CustOut = CustOut, @Destination = Dest, @SortUniqueID = UniqueID FROM dbo.sortRules WHERE (UCC = @UCC_bc)
        SELECT @Lane = LaneMap, @ReasonCode = '00', @UniqueID = @SortUniqueID
        from   dbo.LaneMappingTable1
        where  Customer = @CustOut
               and Destination = @Destination
		If @@RowCount = 0 
		SELECT @Lane = @Reject, @ReasonCode = '04', @UniqueID = @SortUniqueID FROM dbo.SortRules WHERE (UCC = @UCC_bc)
       End
    ELSE IF @DCIDMode = 'V' AND @DC_bc <> @DCID 
      Begin
		SELECT @CustOut = CustOut, @Destination = Dest, @SortUniqueID = UniqueID FROM dbo.sortRules WHERE (UCC = @UCC_bc)
        SELECT @Lane = @Reject, @ReasonCode = '02', @UniqueID = @SortUniqueID
        from   dbo.LaneMappingTable1
        where  Customer = @CustOut
               and Destination = @Destination
		If @@RowCount = 0 
		SELECT @Lane = @Reject, @ReasonCode = '04', @UniqueID = @SortUniqueID FROM dbo.SortRules WHERE (UCC = @UCC_bc)
     
      End
    ELSE
      Begin
		SELECT @CustOut = CustOut, @Destination = Dest, @SortUniqueID = UniqueID FROM dbo.SortRules WHERE (UCC = @UCC_bc)
        SELECT @Lane = LaneMap, @ReasonCode = '01', @UniqueID = @SortUniqueID
        from   dbo.LaneMappingTable1
        where  Customer = @CustOut
               and Destination = @Destination
		If @@RowCount = 0 
		SELECT @Lane = @Reject, @ReasonCode = '04', @UniqueID = @SortUniqueID FROM dbo.SortRules WHERE (UCC = @UCC_bc)
    
      End
END

 

[ESquared]

SELECT doesn't modify variables when no rows are returned. Try this:

DECLARE @a int
SET @a = 1
SELECT @a = val FROM (SELECT 2 AS Val) X WHERE 1 = 0
SELECT @a

The query in parentheses is a derived table, simulating selecting from a real table. See how @a never changed? If you want @a to change even when no rows are returned, here is your syntax:

DECLARE @a int
SET @a = 1
SET @a = (SELECT val FROM (SELECT 2 AS Val) X WHERE 1 = 0)
SELECT @a

So look at your statement:

SELECT @Lane = @Reject, @ReasonCode = '03', @UniqueID = '000' FROM dbo.SortRules WHERE (UCC = @UCC_bc)

What you're doing here is essentially saying, ONLY update @Lane, @ReasonCode, and @UniqueID if there is at least one row in SortRules that meets your where condition. If not, leave 'em NULL. What behavior do you want? Why are you even selecting from SortRules if you're not using any columns from it, except to check for there being a row with the right conditions? In that case, make your code clearer with this logically identical syntax:

IF EXISTS (SELECT * FROM dbo.SortRules WHERE UCC = @UCC_bc) BEGIN
   SET @Lane = @Reject
   SET @ReasonCode = '03'
   SET @UniqueID = '000'
END

(Best practice is to use SET for each variable and to reserve SELECT for queries that hit a table.) If you don't care about what's in SortRules, just set the variables and don't try to access anything from that table. This alternate syntax may make it clear for you what's going on and why you're getting NULLs.

Also, don't repeat things needlessly. Move the two parts repeated three times each before and after your different logic:

      [b]SELECT @CustOut = CustOut, @Destination = Dest, @SortUniqueID = UniqueID FROM dbo.SortRules WHERE UCC = @UCC_bc[/b]
      IF @DC_bc = @DCID AND @DCIDMode = 'V' BEGIN
         SELECT @Lane = LaneMap, @ReasonCode = '00', @UniqueID = @SortUniqueID
         from   dbo.LaneMappingTable1
         where  Customer = @CustOut
               and Destination = @Destination
      END
      ELSE IF @DCIDMode = 'V' AND @DC_bc <> @DCID BEGIN
         SELECT @Lane = @Reject, @ReasonCode = '02', @UniqueID = @SortUniqueID
         from   dbo.LaneMappingTable1
         where  Customer = @CustOut
               and Destination = @Destination
      END
      ELSE BEGIN
         SELECT @Lane = LaneMap, @ReasonCode = '01', @UniqueID = @SortUniqueID
         from   dbo.LaneMappingTable1
         where  Customer = @CustOut
               and Destination = @Destination
      END
      [b]IF @ReasonCode IS NULL -- instead of checking @@Rowcount
         SELECT @Lane = @Reject, @ReasonCode = '04', @UniqueID = @SortUniqueID FROM dbo.SortRules WHERE UCC = @UCC_bc[/b]

Putting it all together, here's what I get:

-- SET NOCOUNT ON added to prevent counts tricking the client DB library into thinking they're result sets
SET NOCOUNT ON;
SELECT @Retailer = Retailer, @Customer = Customer, @ContainerNum = ContNum FROM dbo.Details WHERE CartonID = @UCC_BC
-- Insert statements for procedure here
SELECT @DCID = DCID, @DCIDMode = DCIDMode FROM dbo.SortRules WHERE UCCMode = 'S' AND UCC = @UCC_bc
IF @@RowCount = 0 BEGIN
   SELECT @Lane = LaneMap, @ReasonCode = '03', @UniqueID = '000' FROM dbo.LaneMappingTable1 WHERE Customer = 'Reject' AND Destination = 'Reject'   
END
ELSE BEGIN
   SELECT @CustOut = CustOut, @Destination = Dest, @UniqueID = UniqueID FROM dbo.SortRules WHERE UCC = @UCC_bc
   SET @UniqueID = @SortUniqueID
   IF @DCIDMode = 'V' AND @DC_bc <> @DCID BEGIN
      SELECT @Lane = LaneMap, @ReasonCode = '02' FROM dbo.LaneMappingTable1 WHERE Customer = 'Reject' AND Destination = 'Reject'
   END
   ELSE BEGIN
      SELECT
         @Lane = LaneMap,
         @ReasonCode = CASE WHEN @DC_bc = @DCID AND @DCIDMode = 'V' THEN '00' ELSE '01' END,
      FROM dbo.LaneMappingTable1
      WHERE
         Customer = @CustOut
         AND Destination = @Destination
   
      IF @ReasonCode IS NULL BEGIN-- instead of checking @@Rowcount
         SELECT @Lane = LaneMap, @ReasonCode = '04' FROM dbo.LaneMappingTable1 WHERE Customer = 'Reject' AND Destination = 'Reject'
      END
   END
END

Further reorganization and optimization may be possible. There are several optimizations made here, not just in shortening code but avoiding table access when not needed, and avoiding extra variables when not needed.