CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
riaz.usmani
riaz.usmani

Reputation: 271

Invalid column in stored procedure

I have a query in a stored procedure, it works fine. now I want to add a column the it show error.

My stored procedure code is:

ALTER PROCEDURE dbo.test
      @SDate DATETIME =Null
    , @EDate DATETIME=Null
    ,@period int=Null        
AS BEGIN         
    SET NOCOUNT ON;

    if @period = 1 
    Begin 
       SELECT 
              t.TotalQuote
            , t.QuoteAmount        
            ,t.avgProbQ
            , t2.TotalOrders
            , t2.OrderAmount       
             ,t3.totalSales
            ,t3.Prob
        FROM (SELECT a = 1) a
        CROSS JOIN (
            SELECT 
                  TotalQuote = COUNT(quoteid)
                , QuoteAmount = SUM(totalamount)
                ,avgProbQ=SUM(CloseProbability)/COUNT(CloseProbability)
            FROM dbo.QuoteBase join dbo.OpportunityBase on dbo.QuoteBase.opportunityid=dbo.OpportunityBase.opportunityid   
            WHERE
              Month(dbo.QuoteBase.CreatedOn)=Month(getdate()) And YEAR(dbo.QuoteBase.CreatedOn)=YEAR(GETDATE())
        ) t
        CROSS JOIN (
            SELECT 
                  TotalOrders = COUNT(salesorderid)
                , OrderAmount = SUM(totalamount) 
            FROM dbo.SalesOrderBase join dbo.OpportunityBase on dbo.SalesOrderBase.Opportunityid=dbo.OpportunityBase.Opportunityid 
         Where Month(dbo.SalesOrderBase.CreatedOn)=Month(getdate()) And YEAR(dbo.SalesOrderBase.CreatedOn)=YEAR(GETDATE())
        ) t2
        CROSS Join(
        SELECT
        TotalSales=COUNT(dbo.OpportunityBase.opportunityid)
        ,Prob=SUM(CloseProbability)/COUNT(CloseProbability)

        FROM dbo.OpportunityBase join dbo.SalesorderBase on dbo.SalesOrderBase.Opportunityid=dbo.OpportunityBase.Opportunityid
            WHERE Month(dbo.OpportunityBase.CreatedOn)=Month(getdate()) And YEAR(dbo.OpportunityBase.CreatedOn)=YEAR(GETDATE())
            And dbo.SalesorderBase.StateCode=4
            )t3
    END

It works fine but when I add a new column like t.test, then it shows error

Msg 207, Level 16, State 1, Procedure test, Line 23
Invalid column name 'test'.

If anyone has an idea please share with me

Upvotes: 1

Views: 374

Answers (3)

Rajeev Bera
Rajeev Bera

Reputation: 2019

I am not sure what is your table looked like it seems you are adding test to your stored procedure but its not added in your database table

This is what I can say by looking the error message. Hope it helps

Upvotes: 1

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

You're getting this error because the column test does not exist in this query:

CROSS JOIN (
    SELECT 
          TotalQuote = COUNT(quoteid)
        , QuoteAmount = SUM(totalamount)
        ,avgProbQ=SUM(CloseProbability)/COUNT(CloseProbability)
    FROM dbo.QuoteBase join dbo.OpportunityBase on dbo.QuoteBase.opportunityid=dbo.OpportunityBase.opportunityid   

    WHERE
      Month(dbo.QuoteBase.CreatedOn)=Month(getdate()) And YEAR(dbo.QuoteBase.CreatedOn)=YEAR(GETDATE())
) t

but, if you were to add to that query a column named test then it would succeed. It could be a string literal like 'Some literal value' AS test if necessary.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146499

Not sure what you are trying to do, but guessing, if you are trying to add a column to the output of stored procedure, that is not in the table that the stored procedure is reading data from, then you have to put a literal expression into the select clause, with a defined column name like below: This example uses a string literal, but it can be any datatype... 

 SELECT 'A String literal to be added to output' As  NewColumnName,
      t.TotalQuote
    , t.QuoteAmount        
    ,t.avgProbQ
    , t2.TotalOrders
    , t2.OrderAmount       
     ,t3.totalSales
    ,t3.Prob
  etc....

Upvotes: 0

Related Questions