need help with stored procedure

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

SQL Server Development (2000)

need help with stored procedure

Author

Topic

Landie
Starting Member

8 Posts

Posted - 2008-08-18 : 09:09:49

If have this stored procedure :

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_Nomenclatuur_HeeftSubNom_Invullen AS

-- variabelen
DECLARE @NomenclatuurNr char(8)
DECLARE @Hulp char(8)

Select @Hulp =null

DECLARE NomCursor CURSOR FOR
SELECT distinct nomenclatuurnr FROM ADZNomenclatuur WHERE (len(nomenclatuurnr) < 7) ORDER BY nomenclatuurnr

OPEN NomCursor
FETCH NEXT FROM NomCursor INTO @NomenclatuurNr
WHILE @@FETCH_STATUS = 0
BEGIN
select @Hulp = ""

-- kijk of er subnomenclaturen bestaan
SET @Hulp = (SELECT top 1 nomenclatuurnr FROM dbo.ADZNomenclatuur WHERE (nomenclatuurnr like @NomenclatuurNr + '%') AND (len(nomenclatuurnr) > len(@NomenclatuurNr))

IF @Hulp is not NULL
BEGIN
-- heeft subnomenclaturen
UPDATE ADZNomenclatuur SET HeeftSubNomenclatuur = 1 WHERE
Nomenclatuurnr = @NomenclatuurNr
END

FETCH NEXT FROM NomCursor INTO @NomenclatuurNr

END

CLOSE NomCursor
DEALLOCATE NomCursor

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

When 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 bestaan
SET @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 
GO
CREATE PROCEDURE dbo.Nomenclatuur_HeeftSubNom_Invullen
AS

SET NOCOUNT ON

UPDATE dbo.ADZNomenclatuur
SET HeeftSubNomenclatuur = 1
WHERE LEN(nomenclatuurnr) < 7
    AND EXISTS
    (
        SELECT *
        FROM dbo.ADZNomenclatuur A
        WHERE A.nomenclatuurnr LIKE dbo.ADZNomenclatuur.nomenclatuurnr + '%'
            AND A.nomenclatuurnr <> dbo.ADZNomenclatuur.nomenclatuurnr
    )
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources