CODEWITHSUNDEEP
sql-server
Kevin Sneyers
Kevin Sneyers

Reputation: 45

Incorrect syntax near the keyword 'CURSOR' + @cursor

I try to make this query work with a cursor but I keep getting the following errors:

Incorrect syntax near the keyword 'CURSOR' / Incorrect syntax near @cursor / Incorrect syntax near @cursor / Incorrect syntax near @cursor

Can someone help me please?

Thanks

DECLARE @maxObject INT
DECLARE @maxVcoObject INT
DECLARE @cursor CURSOR
DECLARE @pointerVcontract INT
DECLARE @aantalObjecten INT
SET @maxVcoObject = (SELECT max(p_vco_object) FROM vco_object)
SET @maxObject = (SELECT MAX(p_object) FROM object)
SET @aantalObjecten = (SELECT COUNT(*) FROM vcontract WHERE p_vcontract NOT IN(SELECT p_vcontract FROM vco_object))
SET @maxObject = @maxObject - @aantalObjecten
SET @cursor = CURSOR FOR
SELECT
p_vcontract
FROM
vcontract, vco_partcp WHERE vcontract.p_vcontract = vco_partcp.p_vcontract AND vcontract.p_vcontract NOT IN (SELECT p_vcontract FROM vco_object)
OPEN @cursor
FETCH NEXT FROM @cursor INTO @pointerVContract
WHILE @@FETCH_STATUS = 0
BEGIN 
SET @maxVcoObject = @maxVcoObject + 1
SET @maxObject = @maxObject + 1
INSERT INTO vco_object (p_vco_object, p_vcontract, p_object, create_date, create_user, last_date, last_user)
VALUES (@maxVcoObject, @pointerVContract, @maxObject, getDate(), "MIG", getDate(), "MIG")
FETCH NEXT FROM @cursor INTO @pointerVContract
END
CLOSE @cursor
DEALLOCATE @cursor

Upvotes: 0

Views: 1984

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

You don't need cursor to do this in first place

SET @maxVcoObject = (SELECT Max(p_vco_object)
                     FROM   vco_object)
SET @maxObject = (SELECT Max(p_object)
                  FROM   object)
SET @aantalObjecten = (SELECT Count(*)
                       FROM   vcontract
                       WHERE  p_vcontract NOT IN(SELECT p_vcontract
                                                 FROM   vco_object))
SET @maxObject = @maxObject - @aantalObjecten

INSERT INTO vco_object
            (p_vco_object,p_vcontract,p_object,create_date,create_user,last_date,last_user)
SELECT @maxVcoObject + Row_number()OVER(ORDER BY (SELECT NULL)),
       p_vcontract,
       @maxObject + Row_number()OVER(ORDER BY (SELECT NULL)),
       Getdate(),
       'MIG', --should be single quotes for string literals
       Getdate(),
       'MIG'
FROM   vcontract vc
INNER JOIN  vco_partcp vcp
        ON  vc.p_vcontract = vcp.p_vcontract
     Where vc.p_vcontract NOT IN (SELECT p_vcontract
                                    FROM   vco_object)

Upvotes: 1

Raul
Raul

Reputation: 3131

The issue here is this line:

VALUES (@maxVcoObject, @pointerVContract, @maxObject, getDate(), "MIG", getDate(), "MIG")

Replace it with:

VALUES (@maxVcoObject, @pointerVContract, @maxObject, getDate(), 'MIG', getDate(), 'MIG')

and it should work. TSQL syntax supports only single quotes for string literals.

As others suggested try to avoid cursors.

Upvotes: 0

Related Questions