• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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
0
lancerxe
Asked:
lancerxe
4 Solutions
 
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.
0
 
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)
0
 
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.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
HainKurtSr. System AnalystCommented:
change name BA to BAXXXX and see if the message changes :)
0
 
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...
0
 
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.
0
 
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

0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now