Stored procedure issue

Hello experts. Please let me know what I am doing wrong on the attached SP.

Msg 207, Level 16, State 1, Procedure usp_QA_Defects_All_Business_Areas, Line 71
Invalid column name 'BA'.

BA is the second column on a temp table.
thanks
lance
All-defects-for-1-to-x-months-ac.txt
lancerxeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
Any possibility your temp table was created under a different owner/schema?
When you check for the table prior to delete you check for dbo.tmpQA_DefectsAllBusinessAreas.
You then delete but only reference tmpQA_DefectsAllBusinessAreas.
You also build and insert without the owner name.

And one suggestion, if you do not need the temp table after the stored procedure runs, consider using a true temp table (a table where the first character is a # symbol). Once the stored procedure ends the temp table will automatically be cleaned up.
Kevin CrossChief Technology OfficerCommented:
Maybe it is not actually dropping some old version of the tmpQA table.

Try:
IF OBJECT_ID(N'dbo.tmpQA_DefectsAllBusinessAreas') IS NOT NULL
   BEGIN
   DROP TABLE tmpQA_DefectsAllBusinessAreas;
   END

Open in new window


I do not have your table structure, but this line recognizes BA for me in SSMS as hoovering over tells me details from CREATE TABLE...maybe I am missing it, too, or as I said it is just the IF.
INSERT INTO tmpQA_DefectsAllBusinessAreas(MonthYear, BA, NumOfDefects)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
Good points from Shaun, I thought of that also. Works for me since my user's default schema is dbo, but would not if yours is not.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

HainKurtSr. System AnalystCommented:
change name BA to BAXXXX and see if the message changes :)
HainKurtSr. System AnalystCommented:
and try this

INSERT INTO tmpQA_DefectsAllBusinessAreas(MonthYear, NumOfDefects)
SELECT @Month AS MonthYear, COUNT(*) AS BugNum
         FROM dbo.Bug b
where 1=2

just to see this works or not...
Kevin CrossChief Technology OfficerCommented:
By the way, it would appear you have some duplication going on:
SET @MonthID = @MonthID + 1
        
        IF  @MonthID > @ToMonthID
                  BREAK
            ELSE
                  CONTINUE
       
       END


        SET @MonthID = @MonthID + 1
        
        IF  @MonthID > @ToMonthID
                  BREAK
            ELSE
                  CONTINUE
       
       END

Open in new window


Couple of suggestions:
- fix indentation so  you can more clearly see when each different level begins, i.e., procedure, while loop, if statements, etc.
- use the begin and end with IFs just to ensure the above is clarified in code.
- if you have WHILE @MonthID <= @ToMonthID, the check IF  @MonthID > @ToMonthID to use BREAK is redundant.
Ephraim WangoyaCommented:
why not use an actual temp table as follows
/****** Object:  StoredProcedure [dbo].[usp_QA_Defects_All_Business_Areas]    Script Date: 10/05/2011 16:35:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--********************************************************************************
		-- STORED PROCEDURE NAME: usp_QA_Defects_All_Business_Areas
		-- CREATED BY: X
		-- CREATED DATE: 10/05/2011
		-- DESCRIPTION: Query Project, Type, and number of defects (bugs) for each month
		-- within a range of months specified by 2 parameters of this stored procedure
		-- NOTE: An example to call this sproc:
		-- EXEC  usp_QA_Defects_All_Business_Areas '3/2011', '8/2011', 'CARA'
		-- SELECT * FROM dbo.tmpQA_DefectsAllBusinessAreas
		-- ORDER BY Id
		
		-- Note, for parameters enter '3/2011' not '03/2011'    
        --********************************************************************************
							
		--For testing        
                --DECLARE @FromMonth varchar(7)
		--DECLARE @ToMonth varchar(7)
		--DECLARE @MonthStr 
		--SET @FromMonth = '3/2011'   -- '3/2011' not '03/2011'
		--SET @ToMonth = '8/2011'	
                
		
ALTER PROCEDURE [dbo].[usp_QA_Defects_All_Business_Areas]
  @FromMonth varchar(7),
  @ToMonth varchar(7)
  
AS
	SET NOCOUNT ON
           
    -- Get MonthID
    DECLARE @FromMonthID smallint
    DECLARE @ToMonthID smallint
    DECLARE @Month varchar(7)
    DECLARE @MonthID smallint
                
    SELECT @FromMonthID = MonthID FROM dbo.QA_MonthIDLookup
    WHERE Month = @FromMonth
    -- testing
    --PRINT @FromMonthID  -- 15
    SELECT @ToMonthID = MonthID FROM dbo.QA_MonthIDLookup
    WHERE Month = @ToMonth
    -- testing
    --PRINT @ToMonthID  -- 20
    
    --Execute the following loop, insert results into a temp table after each iteration
    --At the beginning of each iteration, find value of @Month
    
    SET @MonthID = @FromMonthID

        --Create temp table to store results
    IF OBJECT_ID ('tempdb..##tmpQA_DefectsAllBusinessAreas') IS NOT NULL
		DROP TABLE ##tmpQA_DefectsAllBusinessAreas
	
	CREATE TABLE ##@tmpQA_DefectsAllBusinessAreas(
		Id int not null identity primary key,
		MonthYear varchar(7) not null,
		BA varchar(50) not null,
		NumOfDefects int not null
	)
                        
    -- LOOP STARTS HERE
            
    WHILE @MonthID <= @ToMonthID    
    BEGIN
    
		-- Find @Month based on @MonthID
		SELECT @Month = Month FROM dbo.QA_MonthIDLookup
		WHERE MonthID = @MonthID

		INSERT INTO ##tmpQA_DefectsAllBusinessAreas(MonthYear, BA, NumOfDefects)
	       
		--Calculate bugs
      
		SELECT @Month AS MonthYear, K.BusinessLineName, COUNT(*) AS BugNum
        FROM dbo.Bug b          
         INNER JOIN dbo.Iteration i ON i.IterationID = b.IterationID
		 INNER JOIN dbo.Project J ON J.Project = I.Project 
         INNER JOIN dbo.BusinessLine K ON K.BusinessLineCode = J.BusinessLineCode
         WHERE(b.Latest = 1)
         AND b.Deleted = 0
         AND b.CollectionID = 2
         --AND b.DateCreated >= @FromDate
         --AND DateCreated < DateAdd(d, 1, @ToDate)
         AND RTRIM(convert(char(2),DATEPART(M,DateCreated))) + '/' + convert(char(4),DATEPART(YEAR,DateCreated)) = @Month
         
         AND ((b.State = 'Active' AND  b.Triage = 'Triaged') OR b.State = 'Closed')
         GROUP BY K.BusinessLineName
	END

Open in new window

lancerxeAuthor Commented:
Thanks for your sugestions. The problem wes  that the temp file tmpQA_DefectsAllBusinessAreas existed already and it had been created with a column name other than BA. When I deleted the temp file and ran the SP again, it worked fine.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.