T-SQL Cursor query

[johnjrd]

Hi

Am getting the following error message when running this stored procedure :

Server: Msg 156, Level 15, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'from'.



see code below. Any ideas would be great thanks

CREATE procedure CREATE_CSEDAYS
as


set nocount on
declare @lcsid int,@licsid int, @licsesid int, @lccsecode char(15), @lcdayname char(10), @lcsetime char(5), @lilockey int, @dynamicSQL varchar(5000)

declare curcsedaysid cursor
for select max(csedaysid) licsid from csedays

open curcsedaysid

FETCH curcsedaysid into @licsid

set @lcsid = 0

If @licsid = 0
set @lcsid = 1
else
set @lcsid = @licsid+1


declare curcselist cursor
for
select distinct
cs.csesid,csecode,{fn DAYNAME(ss.sesdate)} as dayname,ss.sestime,ss.lockey
from
course cs,sessions ss,syspars sy
where
cs.parkey = sy.parsid and cs.csesid = ss.ss_csekey and sy.paryear = '06 - 07' and cs.csesid not in (select csekey from csedays)


Open curcselist

Fetch NEXT FROM curcselist
into
@licsesid,@lccsecode,@lcdayname,@lcsetime,@lilockey



WHILE @@FETCH_STATUS = 0
BEGIN

-- drop table if it exists
set @DynamicSQL= 'INSERT INTO CSEDAYS (csedaysid,csekey,day,from,lockey) VALUES (@lcsid ,@licsesid ,@lcdayname ,@lcsetime ,@lilockey)'
exec (@dynamicSQL)


set @lcsid = @lcsid +1

FETCH NEXT FROM curcselist
into @licsesid,@lccsecode,@lcdayname,@lcsetime,@lilockey
END

-- Tidy up
CLOSE curcselist
DEALLOCATE curcselist

CLOSE curcsedaysid
DEALLOCATE curcsedaysid
GO

 

[bborissov]

What about not using cursors at all?

CREATE PROCEDURE Create_CseDays
as
set nocount on
select @lcsid = max(csedaysid) licsid from csedays

If @licsid = 0
 set @lcsid = 1
else
 set @lcsid = @licsid+1

INSERT INTO CSEDAYS (csedaysid,csekey,day,from,lockey)
SELECT DISTINCT @lcsid , cs.csesid,csecode, {fn DAYNAME(ss.sesdate)} ,ss.sestime,ss.lockey
       FROM course cs,sessions ss,syspars sy
where cs.parkey = sy.parsid and
      cs.csesid = ss.ss_csekey and
      sy.paryear = '06 - 07'   and
      cs.csesid not in (select csekey from csedays)

not tested



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

This should never be done with a cursor. Look up the insert command and see how you insert using a select statment instead of values. Cursors are performance killers and should be avoided at all costs especially they should never be used for inserts, updates and deletes.

Your problem is probably the fieldname from. From is a key word and should not be used as a fieldname. If it can't be changed, then enclose it in brackets as [from].

Questions about posting. See faq183-874
Click here to help with Hurricane Relief