CODEWITHSUNDEEP
sql-server-2008stored-proceduresparameterscase
sateesh
sateesh

Reputation: 87

Using Case field as parameter column in stored procedures in SQL Server

CREATE PROCEDURE [dbo].[rpt_Report]
   @Product varchar(20) = NULL,
   @From DateTime = null,
   @To DateTime = null
AS
BEGIN
    SELECT 
       CO.PO, CO.DLVRY,   
       CASE
          WHEN co.CUST_ID = '120' THEN '120' 
          WHEN HDR.PO_NO LIKE 'CL%' THEN 'CL'
          WHEN RED.REU = 'SK' THEN 'STOCK' 
          WHEN RED.REU = 'SM' THEN 'ORTS' 
          WHEN co.ITEM = 'PW%' THEN 'CHAIN' 
          WHEN co.ITEM LIKE 'D%' THEN 'VAN' ELSE 'MISC' 
       END AS PRODUCT 
     FROM   
        co AS co    
     INNER JOIN 
        HDR AS HDR ON co.NUMBER = HDR.NUMBER 
     INNER JOIN 
        RED AS RED ON co.ITEM = RED.ITEM 
     WHERE   
        (co.DLVRY BETWEEN @From AND @To) AND (@Product = PRODUCT)
     ORDER BY
        co.DLVRY DESC
END

When I execute it I am getting an error saying

Msg 207, Level 16, State 1, Procedure rpt_Report, Line 34
Invalid column name 'PRODUCT'.

I should use the case field value as parameter in where CLAUSE.

Upvotes: 1

Views: 2167

Answers (2)

Taryn
Taryn

Reputation: 247850

It appears that you are attempting to use the PRODUCT alias in the WHERE clause, which you cannot do. So you can wrap the query in a sub-query to use the alias:

CREATE PROCEDURE [dbo].[rpt_Report]
   @Product varchar(20) = NULL,
   @From DateTime = null,
   @To DateTime = null
AS
BEGIN
    SELECT x.PO, x.DLVRY, x.PRODUCT
    FROM
    (
        SELECT CO.PO, CO.DLVRY,   
           CASE
              WHEN co.CUST_ID = '120' THEN '120' 
              WHEN HDR.PO_NO LIKE 'CL%' THEN 'CL'
              WHEN RED.REU = 'SK' THEN 'STOCK' 
              WHEN RED.REU = 'SM' THEN 'ORTS' 
              WHEN co.ITEM = 'PW%' THEN 'CHAIN' 
              WHEN co.ITEM LIKE 'D%' THEN 'VAN' ELSE 'MISC' 
           END AS PRODUCT 
         FROM  co AS co    
         INNER JOIN HDR AS HDR 
            ON co.NUMBER = HDR.NUMBER 
         INNER JOIN RED AS RED 
            ON co.ITEM = RED.ITEM 
    ) x        
    WHERE (x.DLVRY BETWEEN @From AND @To) 
        AND (@Product = PRODUCT)
    ORDER BY x.DLVRY DESC
END

Upvotes: 1

Kundan Singh Chouhan
Kundan Singh Chouhan

Reputation: 14302

Try this instead :

SELECT 
   CO.PO, CO.DLVRY,   
   PRODUCT = CASE
                WHEN co.CUST_ID = '120' THEN '120' 
                WHEN HDR.PO_NO LIKE 'CL%' THEN 'CL'
                WHEN RED.REU = 'SK' THEN 'STOCK' 
                WHEN RED.REU = 'SM' THEN 'ORTS' 
                WHEN co.ITEM = 'PW%' THEN 'CHAIN' 
                WHEN co.ITEM LIKE 'D%' THEN 'VAN' ELSE 'MISC' 
             END 
 FROM   
    co AS co    
 INNER JOIN 
    HDR AS HDR ON co.NUMBER = HDR.NUMBER 
 INNER JOIN 
    RED AS RED ON co.ITEM = RED.ITEM 
 WHERE   
    (co.DLVRY BETWEEN @From AND @To) AND (@Product = PRODUCT)
 ORDER BY
    co.DLVRY DESC

Hope this will help !!

Upvotes: 0

Related Questions