saoirse1916
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!
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;
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!