Link to home
Start Free TrialLog in
Avatar of saoirse1916
saoirse1916Flag for United States of America

asked on

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?

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

AS

SET NOCOUNT ON;

CREATE TABLE #TempNotifications
(
	ID int IDENTITY,
	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

SELECT *
FROM #TempNotifications
ORDER BY notificationDate ASC, notificationGroupName ASC

SET NOCOUNT OFF;

Open in new window

Avatar of Chris Luttrell
Chris Luttrell
Flag of United States of America image

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?
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of saoirse1916

ASKER

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!