November 30, 2009 at 3:14 am
Just having a small problem in regards to using a temp table in a Stored Procedure, when i try to execute this I am getting an error "incorrect syntax near the keyword FETCH". Not sure whats wrong Im pretty new to using these, im only getting the error when im trying to insert values into the temp table. Thanks
Declare pc_audit_cursor CURSOR
FOR select distinct PC_PROFILE_ID FROM TBL_PC_AUDIT
/* Create temp table */
CREATE TABLE #savingstemp
(
pc_profile_id int,
startup_time datetime,
hibernate_time datetime,
sleep_time datetime,
shutdown_time datetime,
hours_off_total int,
hours_off_day int,
hours_off_night int
)
OPEN pc_audit_cursor
FETCH NEXT FROM pc_audit_cursor into @pc_profile_id
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @pc_profile_id
/**1. Get hours off*/
UPDATE #savingstemp
SET hours_off_total = DATEDIFF(HOUR, @SHUTDOWN_TIME, @STARTUP_TIME)
+ DATEDIFF(HOUR, @STARTUP_TIME, @HIBERNATE_TIME) + DATEDIFF(HOUR, @STARTUP_TIME, @SLEEP_TIME)
WHERE STATUS = 'CLOSED';
/** Insert values into temp table */
INSERT into #savingstemp (pc_profile_id, hours_off_total, hours_off_day, hours_off_night)
FETCH NEXT FROM pc_audit_cursor INTO @pc_profile_id
END
CLOSE pc_audit_cursor
DEALLOCATE pc_audit_cursor
/**Drop temporary table */
drop table #savingstemp
RETURN
November 30, 2009 at 3:20 am
The insert statement right before the second fetch is only half-done. You need to define what to insert (either a VALUES clause or a SELECT statement)
Why a cursor? From the quick look over it, there doesn't seem to be any need for a cursor, this can very likely be done with a single insert and a single update.
I must admit, I don't understand this code, unless you left something out. You're updating a temp table, then inserting into it (shouldn't it be the other way around), then dropping it, not having done anything with the data in it. What's the point?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2009 at 3:30 am
Thanks for getting back to me, ya sorry your right there shouldn't be an "UPDATE" there but i wasn't too sure on how to write this line of code correctly and what syntax to use, i just need to get the DATEDIFF between a lot of times and add them together thats all, maybe i should be using GET instead of SET Im not sure.
UPDATE #savingstemp
SET hours_off_total = DATEDIFF(HOUR, @SHUTDOWN_TIME, @STARTUP_TIME + DATEDIFF(HOUR, @STARTUP_TIME, @HIBERNATE_TIME) + DATEDIFF(HOUR, @STARTUP_TIME, @SLEEP_TIME)
WHERE STATUS = 'CLOSED';
In relation to the 2nd INSERT, i thought the syntax used was correct, but im guessing its not.
INSERT into #savingstemp (pc_profile_id, hours_off_total, hours_off_day, hours_off_night)
Thanks for the reply
November 30, 2009 at 3:42 am
No such thing as a GET in SQL. I think you need to take several steps back and explain what it is that you're trying to achieve here.
The syntax for an insert is
INSERT INTO <Table Name> (Column List)
VALUES (<Values for those columns>)
or
INSERT INTO <Table Name> (Column List)
SELECT <Values for those columns>
FROM <Some Source>
You have only the first line, specifying what table and columns the data has to be inserted into. You've said nothing about where the values to be inserted come from.
Still curious, why insert data into a temp table then drop the temp table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2009 at 4:27 am
Thanks for the reply, Ya maybe your right I should not drop the table, bit of a newbie so just trying to get my head around this, could take a while 🙁
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply