SQLTeam.com | Weblogs | Forums

SQL column data won't display when hard-coded value is replaced with placeholder

sql2005

#1

I am trying to replace place holders with user input.

When the user goes to run the report, a GUI dialog pops up asking them to specify the start date, end date, and customer ID. I have several variables that store expressions to calculate certain outcomes. However, the data in the WHERE statements for the variables is hard-coded in, for example:

@InputWeight - (
    SELECT Sum([ICPL].[OriginalQuantity_Stk])
  FROM IC_Products [PC] 
    INNER JOIN  DC_Transactions [DCT] 
     ON [PC].ProductKey = [DCT].ProductKey
    INNER JOIN  AR_Customers 
     ON [DCT].CustomerKey = AR_Customers.CustomerKey
    INNER JOIN  IC_ProductLots [ICPL]
     ON [DCT].LotKey = [ICPL].LotKey
    LEFT OUTER JOIN  IC_ProductCosts [ICP] 
     ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5
 WHERE 
    ([ICPL].ProductionDate >= { ts '2015-06-24 00:00:00' }   AND ([ICPL].ProductionDate <= { ts '2015-06-24 00:00:00' } OR [ICPL].ProductionDate Is Null)) 
AND (AR_Customers.CustomerCode = 904) 
)

This is problematic because the date will not always be the 24th of June and the customer code may not always be 904.

I created a variable called @sID and made it appear in the GUI dialog box. Then, I set AR_Customers.CustomerCode = @sID. So in the dialog box in the sID field if I enter 904 it will display the proper data.

The problem is, when I attempt to replace 904 with @sID in the WHERE statement for the variables, it doesn't display any data for that field. This is how it looks:

@InputWeight - (
    SELECT Sum([ICPL].[OriginalQuantity_Stk])
  FROM IC_Products [PC] 
    INNER JOIN  DC_Transactions [DCT] 
     ON [PC].ProductKey = [DCT].ProductKey
    INNER JOIN  AR_Customers 
     ON [DCT].CustomerKey = AR_Customers.CustomerKey
    INNER JOIN  IC_ProductLots [ICPL]
     ON [DCT].LotKey = [ICPL].LotKey
    LEFT OUTER JOIN  IC_ProductCosts [ICP] 
     ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5
 WHERE 
    ([ICPL].ProductionDate >= { ts '2015-06-24 00:00:00' }   AND ([ICPL].ProductionDate <= { ts '2015-06-24 00:00:00' } OR [ICPL].ProductionDate Is Null)) 
AND (AR_Customers.CustomerCode = @sID) 
)

When I have the variable expression as it is in my first example it displays correctly, but when I swap out 904 for @sID, nothing appears in that field. What am I doing wrong that it stops displaying the value necessary? Am I on the correct track?

I am using Microsoft SQL Sever 2005.

Full Code if this helps clarify things:

SET NOCOUNT ON; 
DECLARE @PurchaseCost Decimal(19,8);
DECLARE @InputWeight Decimal(19,8);
DECLARE @Shrink Decimal(19,8);
DECLARE @Prod_CostLBS Decimal(19,8);
DECLARE @Cost Decimal(19,8);
DECLARE @Profit Decimal(19,8);
DECLARE @Proj Decimal(19,8);
DECLARE @sID Int;

SET @PurchaseCost = 2.58;
SET @InputWeight = 18100;
SET @Shrink  = @InputWeight - (
 SELECT Sum([ICPL].[OriginalQuantity_Stk])
 FROM IC_Products [PC] 
 INNER JOIN DC_Transactions [DCT] 
 ON [PC].ProductKey = [DCT].ProductKey
 INNER JOIN AR_Customers 
 ON [DCT].CustomerKey = AR_Customers.CustomerKey
 INNER JOIN IC_ProductLots [ICPL] 
 ON [DCT].LotKey = [ICPL].LotKey
 LEFT OUTER JOIN IC_ProductCosts [ICP] 
 ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5
 WHERE (ICPL.ProductionDate >= { ts '2015-06-24 00:00:00' } AND (ICPL.ProductionDate <= { ts '2015-06-24 00:00:00' } OR ICPL.ProductionDate Is Null)) 
AND (AR_Customers.CustomerCode IN (904)) 
);
SET @Prod_CostLBS = 0.15;
SET @Cost  = ROUND((@PurchaseCost + @Prod_CostLBS) * (
 SELECT Sum([ICPL].[OriginalQuantity_Stk])
 FROM IC_Products [PC] 
 INNER JOIN DC_Transactions [DCT] 
 ON [PC].ProductKey = [DCT].ProductKey
 INNER JOIN AR_Customers 
 ON [DCT].CustomerKey = AR_Customers.CustomerKey
 INNER JOIN IC_ProductLots [ICPL] 
 ON [DCT].LotKey = [ICPL].LotKey
 LEFT OUTER JOIN IC_ProductCosts [ICP] 
 ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5
 WHERE (ICPL.ProductionDate >= { ts '2015-06-24 00:00:00' } AND (ICPL.ProductionDate <= { ts '2015-06-24 00:00:00' } OR ICPL.ProductionDate Is Null)) 
AND ((1=1) AND AR_Customers.CustomerKey IN (124)) 
), 2);
SET @Profit  = (
 SELECT SUM(ROUND([DCT].[Quantity_Stk] *[ICP].[UnitCost], 2))
 FROM IC_Products [PC] 
 INNER JOIN DC_Transactions [DCT] 
 ON [PC].ProductKey = [DCT].ProductKey
 INNER JOIN AR_Customers 
 ON [DCT].CustomerKey = AR_Customers.CustomerKey
 INNER JOIN IC_ProductLots [ICPL] 
 ON [DCT].LotKey = [ICPL].LotKey
 LEFT OUTER JOIN IC_ProductCosts [ICP] 
 ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5
 WHERE (ICPL.ProductionDate >= { ts '2015-06-24 00:00:00' } AND (ICPL.ProductionDate <= { ts '2015-06-24 00:00:00' } OR ICPL.ProductionDate Is Null)) 
AND ((1=1) AND AR_Customers.CustomerKey IN (124)) 
) - @Cost;
SET @Proj  = ROUND((@Profit) / (
 SELECT Sum([ICPL].[OriginalQuantity_Stk])
 FROM IC_Products [PC] 
 INNER JOIN DC_Transactions [DCT] 
 ON [PC].ProductKey = [DCT].ProductKey
 INNER JOIN AR_Customers 
 ON [DCT].CustomerKey = AR_Customers.CustomerKey
 INNER JOIN IC_ProductLots [ICPL] 
 ON [DCT].LotKey = [ICPL].LotKey
 LEFT OUTER JOIN IC_ProductCosts [ICP] 
 ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5
 WHERE (ICPL.ProductionDate >= { ts '2015-06-24 00:00:00' } AND (ICPL.ProductionDate <= { ts '2015-06-24 00:00:00' } OR ICPL.ProductionDate Is Null)) 
AND ((1=1) AND AR_Customers.CustomerKey IN (124)) 
), 2)
;
SET @sID = 904;

SELECT DISTINCT 
     CAST([AR_Customers].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([AR_Customers].[Name] AS NVARCHAR(40)) AS [Supplier]
   , [PC].ProductCode
   , [PC].Description1
   , Count(IC_ProductLots.OriginalQuantity_Alt) AS [Boxes]
   , IC_ProductLots.UnitOfMeasure_Alt
   , Sum(IC_ProductLots.OriginalQuantity_Stk) AS [Weight]
   , IC_ProductLots.UnitOfMeasure_Stk
   , [ICP].UnitCost AS [Unit Cost]
   , Sum(ROUND([DCT].[Quantity_Stk] *[ICP].[UnitCost], 2)) AS [Total Sales]
   , Avg(([IC_ProductLots].[OriginalQuantity_Stk] / [IC_ProductLots].[OriginalQuantity_Alt])) AS [Avg. Box Weight]
   , Sum([IC_ProductLots].[OriginalQuantity_Stk] / @InputWeight) AS [Yield]
   , @Shrink AS [Shrink]
   , @Cost AS [Cost]
   , @Profit AS [Profit]
   , @Proj AS [Proj]
   , @sID AS [sID]
 FROM (((( IC_Products [PC] 
    INNER JOIN  DC_Transactions [DCT] 
     ON [PC].ProductKey = [DCT].ProductKey)
    INNER JOIN  AR_Customers 
     ON [DCT].CustomerKey = AR_Customers.CustomerKey)
    INNER JOIN  IC_ProductLots 
     ON [DCT].LotKey = IC_ProductLots.LotKey)
    LEFT OUTER JOIN  IC_ProductCosts [ICP] 
     ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5)
 WHERE 
    (IC_ProductLots.ProductionDate >= { ts '2015-06-24 00:00:00' }   AND (IC_ProductLots.ProductionDate <= { ts '2015-06-24 00:00:00' } OR IC_ProductLots.ProductionDate Is Null)) 
 GROUP BY 
     CAST([AR_Customers].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([AR_Customers].[Name] AS NVARCHAR(40))
   , [PC].ProductCode
   , [PC].Description1
   , IC_ProductLots.UnitOfMeasure_Alt
   , IC_ProductLots.UnitOfMeasure_Stk
   , [ICP].UnitCost
   , IC_ProductLots.ProductionDate
   , AR_Customers.CustomerCode
 HAVING 
    (AR_Customers.CustomerCode = @sID)
 ORDER BY 
     CAST([AR_Customers].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([AR_Customers].[Name] AS NVARCHAR(40))

#2

Are you setting the varibale with the user input?

If you run this before your code provided

SELECT  @sID

What value does it return?


#3

Yes, it returns the value 904 which is what was input by the user.

Also, if I change it to a different number in the user input box, say 47 it will display all of the information for the supplier with the ID 47.


#4

I'd suggest you break it down as much as you can. If you run the code in a separate query window

DECLARE	@sID INT;

SET @sID = 904;
--SET @sID = 47;

    SELECT	SUM([ICPL].[OriginalQuantity_Stk])
    FROM	IC_Products [PC] 
    INNER
    JOIN	DC_Transactions [DCT] 
    		ON [PC].ProductKey = [DCT].ProductKey
    INNER
    JOIN	AR_Customers 
    		ON [DCT].CustomerKey = AR_Customers.CustomerKey
    INNER
    JOIN	IC_ProductLots [ICPL]
    		ON [DCT].LotKey = [ICPL].LotKey
    LEFT	OUTER
    JOIN	IC_ProductCosts [ICP] 
    		ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5
    WHERE	AR_Customers.CustomerCode = @sID
    		AND	(
    				ICPL.ProductionDate >= '20150624'
    				AND ICPL.ProductionDate <= '20150624'
    				OR [ICPL].ProductionDate IS NULL
    			);

Does it still not return any results?


#5

The problem with running it in a separate query window is that this is done in a system where I can't directly edit the code. Therefore, I wouldn't be able to just create that segment of the code in a separate query. I also can not directly edit the whole queries WHERE clause. I can only edit the WHERE clause for the variables. Does that make sense?