Invalid column name in #temp table

I've got a query that dumps data to a temp table, then uses a WHILE loop to update a field in that temp table.  When I execute the SQL in Query Analyzer everything runs just fine, but when I call the stored procedure I get "Invalid column name 'notificationGroupName'" pointing to the initial insert into the temp table.  I also get another of the same error pointing to my ORDER BY clause when I'm finally selecting all the data in the end.  Can anyone point me in the right direction?

ALTER PROCEDURE [dbo].[sproc_dashboard_select_notifications]
	@UserID int



CREATE TABLE #TempNotifications
	notificationID int,
	notificationDate datetime,
	notificationGroupName varchar(250),
	processStepID int,
	coID varchar(50),
	suiteID int,
	leaseID int,
	assetID int,
	woID int,
	notificationRelatesTo varchar(250)

--	Save all applicable notifications to a temp table
INSERT INTO #TempNotifications (notificationID, notificationDate, notificationGroupName, processStepID, coID, suiteID, leaseID, assetID, woID)
	SELECT notificationID, notificationDate, notificationGroupName, processStepID, coID, suiteID, leaseID, assetID, woID
	FROM tblNotifications
	JOIN tblNotificationGroupLevel ON tblNotifications.notificationGroupLevelID = tblNotificationGroupLevel.notificationGroupLevelID
	JOIN tblNotificationGroup ON tblNotificationGroupLevel.notificationGroupID = tblNotificationGroup.notificationGroupID
	WHERE tblNotifications.userID = @UserID AND notificationStatus = 0

FROM #TempNotifications
ORDER BY notificationDate ASC, notificationGroupName ASC


Open in new window

Who is Participating?
LowfatspreadConnect With a Mentor Commented:
try qualifiying all the column names on the select statement....
are you sure you haven't got a duplicate?

are you also sure that you dropped the temp table before executing the procedure?

INSERT INTO #TempNotifications
 (notificationID, notificationDate, notificationGroupName, processStepID, coID, suiteID, leaseID, assetID, woID)
      SELECT notificationID, notificationDate, ng.notificationGroupName
               , processStepID, coID, suiteID, leaseID, assetID, woID
      FROM tblNotifications as n
      JOIN tblNotificationGroupLevel as NGL
          ON N.notificationGroupLevelID = ngl.notificationGroupLevelID
      JOIN tblNotificationGroup as NG
          ON NGL.notificationGroupID = NG.notificationGroupID
      WHERE N.userID = @UserID
          AND notificationStatus = 0
Chris LuttrellSenior Database ArchitectCommented:
I tested what I could on your temp table and the syntax works fine.  I am thinking it is something about your Select that it is not liking.  If you are testing it can you comment out your insert and just run the select?  Check the table and column names and is there some difference in permissions when you run the SQL yourself and when you execute the statement to create the Stored Procedure?
saoirse1916Author Commented:
Well, it must have had something to do with dropping the table -- I just added in a DROP TABLE #TempNotifications and it said that it didn't exist, so I took out that line and ran the SP and it worked fine.  Odd...thanks for the suggestions!
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.

All Courses

From novice to tech pro — start learning today.