Avatar of ITMikeK
ITMikeKFlag for United States of America asked on

quotes in SQL Statement

I have a select statement in a SP in an SQL 2008 database that contains the following:

SUM(case when b.TrxType = 'R' AND b.LbsTransferred =  0 then b.NoOfCoils else 0 end) As Received

If I have this outside the stored procedure, it works fine...but inside the procedure it states that there is incorrect syntax near 'R'.
If I change the 'R' to ' 'R' ' the error goes away , but the procedure does not return the value of R.

Any ideas?
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft Development

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
PortletPaul

are you using dynamic SQL?
ASKER
ITMikeK

Full statement:
 (
        @chkInventory int, @txtCustomerNumber varchar(MAX), @strGrade varchar(max), @strRodSize varchar(max), @strSecondaryRodSize varchar(MAX), @strHeat varchar(max)
)
as
 
BEGIN
 
     
        SET NOCOUNT ON;
 
        DECLARE      @return_value int,
            @strAllocateSQL varchar(max)
   SET @strHeat = NULLIF(@strHeat,'')
       
     

        set @strAllocateSQL = 'SELECT x.* ,(Received-Shipped-Transferred) AS OnHand
                  ,(Received-Allocated) AS Available
                  ,case when Received>0 then cast(Weight/ case when (Received-Shipped-Transferred) = 0 then NULL else (Received-Shipped-Transferred) end as int) else 0 end AS AvgWt
                  FROM (SELECT b.HeatID,b.Customer AS Customer,a.GradeType,a.RodSize,a.HeatNumber,a.Grade,a.VendorNumber,a.Consignment,a.Vendor,a.CustomerNumber,a.CustomerPO, a.PremiumRod,
                  SUM(case when b.TrxType = 'R' AND b.LbsTransferred =  0 then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = 'S'  AND b.TransferToCustomer = 0 then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = 'A' then b.NoOfCoils else 0 end) As Allocated
                   ,SUM(case when b.TrxType = 'R'  AND b.LbsTransferred > 0 then b.NoOfCoils else 0 end) As TransferredIn
                  ,SUM(case when b.TrxType = 'S' AND b.TransferToCustomer > 0 then b.CoilsShipped else 0 end) As TransferredOut
                  ,SUM(case when b.TrxType = 'S' AND b.TransferToCustomer > 0 then b.CoilsShipped else 0 end) As Transferred
                  ,SUM(b.LbsRecd-b.LBSShipped+b.LbsTransferred) As Weight
                  ,MIN(case when b.TrxType = 'R' then b.TrxDate end) As EarlyDate
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID
                              
                         
                   WHERE 1 = 1 '
 
                 if( @chkInventory = 0 )  
                  begin
                   
                     set @strAllocateSQL = @strAllocateSQL + ' AND (b.TransferFromCustomer IN (70,79,74,81,97) OR b.CustomerNumber  IN (70,79,74,81,97) OR b.CustomerNumber =  cast( ' + @txtCustomerNumber + 'as int) ) ' -- cast( ' + @txtCustomerNumber + 'as int) '

                    if (@strGrade != '')

                      begin

                       set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                           end        
                     
                     

                     if( @strRodSize != '')
                        begin
                          SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                        end
         
                       
                   end
 
           
                  set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,b.Customer,a.RodSize,a.HeatNumber,a.Grade,a.VendorNumber,a.Consignment,a.GradeType,a.Vendor,a.CustomerNumber, a.CustomerPO, a.PremiumRod) AS x WHERE (Received-Allocated)  > 0'
 
   
 
 
 
    EXEC(@strAllocateSQL )
    PRINT @strAllocateSQL
 
END
Anthony Perkins

As alluded to previously (no points please) since you are using dynamic sql you have to escape any single quotes.  This is true in a Stored Procedure as well as SQL Script.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
ITMikeK

If I change
SUM(case when b.TrxType = 'R' AND b.LbsTransferred =  0 then b.NoOfCoils else 0 end) As Received,

to
SUM(case when b.TrxType =' 'R'' AND b.LbsTransferred =  0 then b.NoOfCoils else 0 end) As Received,

the R never sums
Anthony Perkins

Incidentally, there is no need to use Dynamic SQL here.  There are three reasons for that:
1. Typically bad performance.
2. Lousy security.  Aside from the risk of SQL Injection you will have to GRANT SELECT permissions to the user that runs this Stored Procedure.  This is a bad idea.
3. Nightmare to maintain.
Anthony Perkins

This is how you would do it without using Dynamic SQL (and yes you could combine those two queries into one, however I would not recommend it):
CREATE PROCEDURE usp_YourStoredProedure
    @chkInventory int,
    @txtCustomerNumber varchar(MAX),	-- Don't use varchar(MAX) where it is not appropriate:
    @strGrade varchar(MAX),		-- Are you really going to pass in up to 2 billion characters?
    @strRodSize varchar(MAX),
    @strSecondaryRodSize varchar(MAX),	-- not used
    @strHeat varchar(MAX)		-- not used

AS 

SET NOCOUNT ON; 

--DECLARE @return_value int,		-- not used
--    @strAllocateSQL varchar(max)
--SET @strHeat = NULLIF(@strHeat, '')

IF @chkInventory = 0 
    SELECT  x.*,	-- Don't use *, columns should be explicitly named
            x.Received - x.Shipped - x.Transferred AS OnHand,
            x.Received - x.Allocated AS Available,
            CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
                                                                 ELSE x.Received - x.Shipped - x.Transferred
                                                            END AS int)
                 ELSE 0
            END AS AvgWt
    FROM    (SELECT b.HeatID,
                    b.Customer AS Customer,
                    a.GradeType,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Received,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Shipped,
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) AS Allocated,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS TransferredIn,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS TransferredOut,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Transferred,
                    SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
                    MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
                        END) AS EarlyDate
             FROM   tblHeat AS a
                    LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
             WHERE  (b.TransferFromCustomer IN (70, 79, 74, 81, 97)
                     OR b.CustomerNumber IN (70, 79, 74, 81, 97)
                     OR b.CustomerNumber = CAST(@txtCustomerNumber AS int)
                    )
                    AND (@strGrade = ''
                         OR a.Grade = @strGrade
                        )
                    AND (@strRodSize = ''
                         OR a.RodSize = @strRodSize
                        )
             GROUP BY b.HeatID,
                    b.Customer,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.GradeType,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod
             HAVING Received - Allocated > 0
            ) AS x

ELSE 
    SELECT  x.*,	-- Don't use *, columns should be explicitly named
            x.Received - x.Shipped - x.Transferred AS OnHand,
            x.Received - x.Allocated AS Available,
            CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
                                                                 ELSE x.Received - x.Shipped - x.Transferred
                                                            END AS int)
                 ELSE 0
            END AS AvgWt
    FROM    (SELECT b.HeatID,
                    b.Customer AS Customer,
                    a.GradeType,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Received,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Shipped,
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) AS Allocated,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS TransferredIn,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS TransferredOut,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Transferred,
                    SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
                    MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
                        END) AS EarlyDate
             FROM   tblHeat AS a
                    LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
             GROUP BY b.HeatID,
                    b.Customer,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.GradeType,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod
             HAVING Received - Allocated > 0
            ) AS x

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Racim BOUDJAKDJI

As acperkins said, if you can avoid using dynamic SQL, don't use it.  That said, dynamic SQL being outside the context of upper level caller procedure, you can get variables back using sp_executesql stored proc.  For more info:

http://technet.microsoft.com/en-us/library/ms188001.aspx
ASKER
ITMikeK

Just tried to run your example (thanks BTW!), but I'm getting an "Invalid Column names" in the HAVING clause.  Any thoughts?
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ITMikeK

Initial testing are giving expected results.  I really appreciate the quick response and explanation.  Super job!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
ITMikeK

I have the following SP I am using.
All my "S" records come back as "0"
USE [OrderEntry_prodSQL]
GO

/****** Object:  StoredProcedure [dbo].[usp_YourStoredProedure2]    Script Date: 10/16/2013 18:04:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_YourStoredProedure2]
    @chkInventory int,
    @txtCustomerNumber varchar(10),	
    @strGrade varchar(10),		
    @strRodSize varchar(8)

AS 

SET NOCOUNT ON; 


IF @chkInventory = 0 
    SELECT  x.*,
            x.Received - x.Shipped - x.Transferred AS OnHand,
            x.Received - x.Allocated AS Available,
            CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
                                                                 ELSE x.Received - x.Shipped - x.Transferred
                                                            END AS int)
                 ELSE 0
            END AS AvgWt
    FROM    (SELECT b.HeatID,
                    b.Customer AS Customer,
                    a.GradeType,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Received,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Shipped,
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) AS Allocated,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS TransferredIn,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS TransferredOut,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.TransferToCustomer > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Transferred,
                    SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
                    MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
                        END) AS EarlyDate
             FROM   tblHeat AS a
                    LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
             WHERE  (b.TransferFromCustomer IN (70, 79, 74, 81, 97,10)
                     OR b.CustomerNumber IN (70, 79, 74, 81, 97,10)
              
                    )
                    AND (@strGrade = ''
                         OR a.Grade = '1022'
                        )
                    AND (@strRodSize = ''
                         OR a.RodSize = '1/4'
                        )
             GROUP BY b.HeatID,
                    b.Customer,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.GradeType,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod
             HAVING SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) -
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) > 0
            ) AS x

ELSE 
    SELECT  x.*,	-- Don't use *, columns should be explicitly named
            x.Received - x.Shipped - x.Transferred AS OnHand,
            x.Received - x.Allocated AS Available,
            CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
                                                                 ELSE x.Received - x.Shipped - x.Transferred
                                                            END AS int)
                 ELSE 0
            END AS AvgWt
    FROM    (SELECT b.HeatID,
                    b.Customer AS Customer,
                    a.GradeType,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Received,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Shipped,
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) AS Allocated,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS TransferredIn,
                  SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS TransferredOut,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.TransferToCustomer > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Transferred,
                    SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
                    MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
                        END) AS EarlyDate
             FROM   tblHeat AS a
                    LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
             GROUP BY b.HeatID,
                    b.Customer,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.GradeType,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod
             HAVING SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) -
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) > 0
            ) AS x
GO

Open in new window

When I isolate
      the routine:
      
      
 select a.HeatNumber,b.RodSize,b.Customer ,a.HeatNumber,a.Consignment,b.Grade,
 
 SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Shipped
                                    FROM   tblHeatTransaction AS b
                     JOIN tblHeat AS a ON a.ID = b.HeatID
                                    WHERE (b.TransferFromCustomer IN (70, 79, 74, 81, 97,10)
                     OR b.CustomerNumber IN (70, 79, 74, 81, 97,10)) and a.RodSize = '1/4' AND a.Grade = '1022'
                                    GROUP BY
                                    b.HeatNumber,
                                    a.HeatNumber,
                    b.Customer,
                    b.RodSize,
                   
                    b.Grade,
                              a.Consignment
            ( I reveresed tblHeat and tblHeatTransaction) in this example, I get values in the shipped column.  If
I put it all together, I get zip.
PortletPaul

I don't see much point in the left join (perhaps there is, just that I'm not seeing it) as all case expressions are based on values in tblHeatTransaction. So either you just move to an inner join and/or reverse the tables (I did both below).

If the left join really is required, then your outer query should probably use ISNULL() and/or you may have to reconsider all the case expressions to cater for nulls.

Also:
You could remove the IF/ELSE test on @chkInventory by joining that condition into the where clause and hence reduce the amount of code being tested.
I have also moved the case expressions around so tests on b.TrxType are in A,R,S sequence (not a necessary thing I just found it easier to read)

CREATE PROCEDURE usp_YourStoredProedure @chkInventory int,
@txtCustomerNumber varchar(MAX), -- Don't use varchar(MAX) where it is not appropriate:
@strGrade varchar(MAX), -- Are you really going to pass in up to 2 billion characters?
@strRodSize varchar(MAX),
@strSecondaryRodSize varchar(MAX), -- not used
@strHeat varchar(MAX) -- not used

AS

        SET NOCOUNT ON;

        --DECLARE @return_value int,		-- not used
        --    @strAllocateSQL varchar(max)
        --SET @strHeat = NULLIF(@strHeat, '')

        SELECT
                x.HeatID
              , x.Customer
              , x.GradeType
              , x.RodSize
              , x.HeatNumber
              , x.Grade
              , x.VendorNumber
              , x.Consignment
              , x.Vendor
              , x.CustomerNumber
              , x.CustomerPO
              , x.PremiumRod
              , x.Allocated         --<< if left joining use ISNULL(x.Allocated) as Allocated
              , x.Received
              , x.TransferredIn
              , x.Transferred
              , x.EarlyDate
              , x.Shipped
              , x.TransferredOut
              , x.Weight
              , x.Received - x.Shipped - x.Transferred AS OnHand
              , x.Received - x.Allocated               AS Available
              , CASE
                        WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE
                                WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
                                ELSE x.Received - x.Shipped - x.Transferred
                                END AS int)
                        ELSE 0
                        END                            AS AvgWt
        FROM (
                SELECT
                        b.HeatID
                      , b.Customer
                      , a.GradeType
                      , a.RodSize
                      , a.HeatNumber
                      , a.Grade
                      , a.VendorNumber
                      , a.Consignment
                      , a.Vendor
                      , a.CustomerNumber
                      , a.CustomerPO
                      , a.PremiumRod
                      , SUM(CASE
                                WHEN b.TrxType = 'A' THEN b.NoOfCoils
                                ELSE 0
                                END)                                     AS Allocated
                      , SUM(CASE
                                WHEN b.TrxType = 'R'
                                AND b.LbsTransferred = 0 THEN b.NoOfCoils
                                ELSE 0
                                END)                                     AS Received
                      , SUM(CASE
                                WHEN b.TrxType = 'R'
                                AND b.LbsTransferred > 0 THEN b.NoOfCoils
                                ELSE 0
                                END)                                     AS TransferredIn
                      , SUM(CASE
                                WHEN b.TrxType = 'R'
                                AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                                ELSE 0
                                END)                                     AS Transferred
                      , MIN(CASE
                                WHEN b.TrxType = 'R' THEN b.TrxDate
                                END)                                     AS EarlyDate
                      , SUM(CASE
                                WHEN b.TrxType = 'S'
                                AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                                ELSE 0
                                END)                                     AS Shipped
                      , SUM(CASE
                                WHEN b.TrxType = 'S'
                                AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                                ELSE 0
                                END)                                     AS TransferredOut
                      , SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight
                FROM tblHeatTransaction AS b
                INNER JOIN tblHeat AS a ON a.ID = b.HeatID
                WHERE ( @chkInventory = 0
                        AND     (
                                   b.TransferFromCustomer IN (70, 79, 74, 81, 97)
                                OR b.CustomerNumber IN (70, 79, 74, 81, 97)
                                OR b.CustomerNumber = CAST(@txtCustomerNumber AS int)
                                )
                        AND (@strGrade = '' OR a.Grade = @strGrade)
                        AND (@strRodSize = '' OR a.RodSize = @strRodSize)
                      )
                      OR
                      ( @chkInventory <> 0 OR @chkInventory IS NULL )
                GROUP BY
                         b.HeatID
                       , b.Customer
                       , a.RodSize
                       , a.HeatNumber
                       , a.Grade
                       , a.VendorNumber
                       , a.Consignment
                       , a.GradeType
                       , a.Vendor
                       , a.CustomerNumber
                       , a.CustomerPO
                       , a.PremiumRod
                HAVING SUM(CASE
                                WHEN b.TrxType = 'R'
                                AND b.LbsTransferred = 0 THEN b.NoOfCoils
                                ELSE 0
                                END) 
                       - SUM(CASE
                                WHEN b.TrxType = 'A' THEN b.NoOfCoils
                                ELSE 0
                                END)
                       > 0
        ) AS x
GO

Open in new window