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.
Author |
Topic |
Landie
Starting Member
8 Posts |
Posted - 2008-08-18 : 09:09:49
|
If have this stored procedure :SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE sp_Nomenclatuur_HeeftSubNom_Invullen AS-- variabelenDECLARE @NomenclatuurNr char(8)DECLARE @Hulp char(8)Select @Hulp =nullDECLARE NomCursor CURSOR FORSELECT distinct nomenclatuurnr FROM ADZNomenclatuur WHERE (len(nomenclatuurnr) < 7) ORDER BY nomenclatuurnrOPEN NomCursorFETCH NEXT FROM NomCursor INTO @NomenclatuurNrWHILE @@FETCH_STATUS = 0BEGINselect @Hulp = ""-- kijk of er subnomenclaturen bestaanSET @Hulp = (SELECT top 1 nomenclatuurnr FROM dbo.ADZNomenclatuur WHERE (nomenclatuurnr like @NomenclatuurNr + '%') AND (len(nomenclatuurnr) > len(@NomenclatuurNr)) IF @Hulp is not NULLBEGIN -- heeft subnomenclaturen UPDATE ADZNomenclatuur SET HeeftSubNomenclatuur = 1 WHERE Nomenclatuurnr = @NomenclatuurNrENDFETCH NEXT FROM NomCursor INTO @NomenclatuurNrENDCLOSE NomCursorDEALLOCATE NomCursorGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOWhen I check the syntax I get this error :Error 1038: Cannot use empty object of column names. Use a single space if necessary.Incorrect syntax near the keyword 'IF'.What can this be ? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-18 : 09:50:31
|
shouldnt this WHERE (nomenclatuurnr like @NomenclatuurNr)be WHERE (nomenclatuurnr like @NomenclatuurNr + '%') |
 |
|
Landie
Starting Member
8 Posts |
Posted - 2008-08-18 : 09:52:38
|
You are right. I've changed this but I still get the same error |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-18 : 09:55:32
|
[code]-- kijk of er subnomenclaturen bestaanSET @Hulp = (SELECT top 1 nomenclatuurnr FROM dbo.ADZNomenclatuur WHERE (nomenclatuurnr like @NomenclatuurNr + '%') AND (len(nomenclatuurnr) > len(@NomenclatuurNr)))[/code] KH[spoiler]Time is always against us[/spoiler] |
 |
|
Landie
Starting Member
8 Posts |
Posted - 2008-08-18 : 09:56:58
|
khtan, thanks, this solved the 'if' problem. Now only the other error appears. |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-18 : 09:59:43
|
what are the errors messages ? KH[spoiler]Time is always against us[/spoiler] |
 |
|
Landie
Starting Member
8 Posts |
Posted - 2008-08-18 : 10:01:43
|
Error 1038: Cannot use empty object of column names. Use a single space if necessary. |
 |
|
Landie
Starting Member
8 Posts |
Posted - 2008-08-19 : 08:06:51
|
Found it. select @Hulp = ""should be :select @Hulp = '' |
 |
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2008-08-20 : 09:13:22
|
This may be better:SET QUOTED_IDENTIFIER, ANSI_NULLS ON GOCREATE PROCEDURE dbo.Nomenclatuur_HeeftSubNom_InvullenASSET NOCOUNT ONUPDATE dbo.ADZNomenclatuurSET HeeftSubNomenclatuur = 1WHERE LEN(nomenclatuurnr) < 7 AND EXISTS ( SELECT * FROM dbo.ADZNomenclatuur A WHERE A.nomenclatuurnr LIKE dbo.ADZNomenclatuur.nomenclatuurnr + '%' AND A.nomenclatuurnr <> dbo.ADZNomenclatuur.nomenclatuurnr )GO |
 |
|
|
|
|