finding syntax error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

finding syntax error

Author

Topic

Finarfin
Starting Member

28 Posts

Posted - 2004-06-07 : 05:11:23

Hello,

That query generates the 156 error: "incorrect syntax near the keyword 'where':

insert into OSCQTZP
(ZP_CODE_PROTOCOLE, ZP_NUM_AVENANT, ZP_CREATEUR, ZP_DATE_CREATION, ZP_DATE_DEBUT_PERIODE, ZP_DATE_FIN_PERIODE, ZP_STATUT, ZP_INDICE_STATUT, ZP_DATE_MAJ, ZP_ID_CLIENT, ZP_ID_DEVISE)
values
(
@codeProtocole,
@numAvenant,
'reprise',
CAST(@annee AS varchar)+'0101',
CAST(@annee AS varchar)+'0101',
CAST(@annee AS varchar)+'1231',
@statut,
@indiceStatut,
CAST(@annee AS varchar)+'0101',
@idClient,
@devise
)
where
@numAvenant, @statut, @indiceStatut not in (
select ZP_NUM_AVENANT, ZP_STATUT, ZP_INDICE_STATUT
from OSCQTZP
where ZP_CODE_PROTOCOLE = @codeProtocole)

I can't find the error, can you please help me?

Thank you all,
Romain

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-07 : 05:23:22

Look in bol for the syntax of the insert command

insert tbl (cols)
values (values)

insert tbl
select cols
from tbl
where ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Finarfin
Starting Member

28 Posts

Posted - 2004-06-07 : 05:40:18

ok,
I tried:

insert into OSCQTZP
(ZP_CODE_PROTOCOLE, ZP_NUM_AVENANT, ZP_CREATEUR, ZP_DATE_CREATION, ZP_DATE_DEBUT_PERIODE, ZP_DATE_FIN_PERIODE, ZP_STATUT, ZP_INDICE_STATUT, ZP_DATE_MAJ, ZP_ID_CLIENT, ZP_ID_DEVISE)

select
@codeProtocole,
@numAvenant,
'reprise',
CAST(@annee AS varchar)+'0101',
CAST(@annee AS varchar)+'0101',
CAST(@annee AS varchar)+'1231',
@statut,
@indiceStatut,
CAST(@annee AS varchar)+'0101',
@idClient,
@devise
where
@numAvenant, @statut, @indiceStatut not in (select ZP_NUM_AVENANT, ZP_STATUT, ZP_INDICE_STATUT
from OSCQTZP
where ZP_CODE_PROTOCOLE = @codeProtocole)

and this generates error 170: incorrect syntax near ',' in the line where
@numAvenant, @statut, @indiceStatut ...
is that possible to do that kind of test? or do I have to text one by one?

Thank you all,
Romain

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-07 : 05:55:45

Have a look in bol. You still have an incorrect syntax.

insert tbl
select cols
from tbl
where ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Finarfin
Starting Member

28 Posts

Posted - 2004-06-07 : 06:18:41

ok,
I think I will just change my query by:

...
where
@numAvenant not in (
select ZP_NUM_AVENANT from OSCQTZP
where ZP_CODE_PROTOCOLE = @codeProtocole
and ZP_STATUT = @statut
and ZP_INDICE_STATUT = @indiceStatut
)

this works, but I dont like it bcause its not pretty.

Thank you all,
Romain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources