[Microsoft SQL] Stored Procedure geeft foutmelding

[JamesTiberius]

Ik ben een stored procedure aan het maken die voor een complete Microsoft SQL (7) database alle lege strings update naar NULL.

Om dit met behulp van een stored procedure te doen is compleet nieuw voor mij, maar dit lijkt mij de beste oplossing.

Ik ben met behulp van voorbeelden gaan programmeren en dit is mijn resultaat:

code:

CREATE PROCEDURE [James].[NEWNULL] AS

DECLARE RS CURSOR FOR SELECT NAME,ID FROM SYSOBJECTS WHERE XTYPE='u'
OPEN RS

DECLARE @TABEL_NAAM Varchar(100)
DECLARE @TABEL_ID Varchar(100)

FETCH NEXT FROM RS INTO @TABEL_NAAM, @TABEL_ID

WHILE @@FETCH_STATUS = 0
BEGIN

  DECLARE RS2 CURSOR FOR SELECT NAME,ID FROM SYSCOLUMNS WHERE ID=@TABEL_ID
  OPEN RS2
  
  DECLARE @KOLOM_NAAM Varchar(100)
  DECLARE @KOLOM_ID Varchar(100)  

  FETCH NEXT FROM RS2 INTO @KOLOM_NAAM, @KOLOM_ID
  
  WHILE @@FETCH_STATUS = 0
  BEGIN

   DECLARE @TABEL_NAAM2  VARCHAR(100)

    DECLARE RS3 CURSOR FOR SELECT @KOLOM_NAAM FROM @TABEL_NAAM
    OPEN RS3

    DECLARE @VELDWAARDE Varchar(100)

    FETCH NEXT FROM RS3 INTO @VELDWAARDE
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    If rtrim(@VELDWAARDE)='' UPDATE @TABEL_NAAM SET @KOLOM_NAAM=NULL WHERE @KOLOM_NAAM=@VELDWAARDE
    
    FETCH NEXT FROM RS3 INTO @VELDWAARDE
    END
    CLOSE RS3
    DEALLOCATE RS3
    
  FETCH NEXT FROM RS2 INTO @KOLOM_NAAM, @KOLOM_ID
  END
  CLOSE RS2
  DEALLOCATE RS2

FETCH NEXT FROM RS INTO @TABEL_NAAM, @TABEL_ID
END
CLOSE RS
DEALLOCATE RS
GO

Nu geeft SQL server mij de foutmelding:
"must declare the variable '@TABEL_NAAM'" en
"Incorrect syntax near the keyword 'WHERE'"

Hij heeft het hierbij over de variabelen in het volgende gedeelte:

code:

    DECLARE RS3 CURSOR FOR SELECT @KOLOM_NAAM FROM @TABEL_NAAM
    OPEN RS3

    DECLARE @VELDWAARDE Varchar(100)

    FETCH NEXT FROM RS3 INTO @VELDWAARDE
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    If rtrim(@VELDWAARDE)='' UPDATE @TABEL_NAAM SET @KOLOM_NAAM=NULL WHERE @KOLOM_NAAM=@VELDWAARDE

Ik snap hier dus echt niets van, ik heb de variabelen toch al ge-declared???

Kan iemand mij helpen?

[Verwijderd]

1) Nooit stored procedures en/of tables aanmaken met jezelf als owner, altijd dbo nemen als owner.
2) SELECT * FROM @Tablename kan niet in T-SQL. Je zult hiervoor een custom string moeten maken en die uitvoeren middels sp_executesql extended stored procedure. Dit lijkt me niet de juiste weg.

Het probleem dat je probeert op te lossen lijkt me ook iewat raar en/of zeldzaam, waarom wil je alle string columns naar 'NULL' omzetten?

(je zou alle columns die je moet omzetten met de tablenaam in een temptable kunnen zetten en dan die aflopen met een cursor, de velden daar uitlezen en daarvan een UPDATE query bouwen in de vorm van een string, die executeren middels sp_executesql )

[JamesTiberius]

Op vrijdag 03 mei 2002 12:24 schreef Otis het volgende:
1) Nooit stored procedures en/of tables aanmaken met jezelf als owner, altijd dbo nemen als owner.
2) SELECT * FROM @Tablename kan niet in T-SQL. Je zult hiervoor een custom string moeten maken en die uitvoeren middels sp_executesql extended stored procedure. Dit lijkt me niet de juiste weg.

Het probleem dat je probeert op te lossen lijkt me ook iewat raar en/of zeldzaam, waarom wil je alle string columns naar 'NULL' omzetten?

(je zou alle columns die je moet omzetten met de tablenaam in een temptable kunnen zetten en dan die aflopen met een cursor, de velden daar uitlezen en daarvan een UPDATE query bouwen in de vorm van een string, die executeren middels sp_executesql )

Punt 1: Check! (en bedankt natuurlijk)
Punt 2:
- Het NULL zetten is nodig voor de applicatie die van de betreffende database gebruik maakt.
- De database is 2GB groot, temptabellen zijn dus geen optie De omvang van de database is ook de reden voor het gebruik maken van een stored procedure. Ik kan deze functie namelijk wel uitvoeren in ASP (waar ik normaalgesproken mee werk) maar dan is hij een klein weekje bezig ben ik bang .
- Je zegt dat die "sp_executesql extended stored procedure" waarschijnlijk geen goede oplossing is. Weet je toevallig geen andere manier om variabel query's samen te stellen en uit te voeren ??

[JamesTiberius]

Dus de vraag is nu eigenlijk: Weet iemand een manier om een variabele query uit te voeren en een resultset terug te krijgen in de bovenstaande stored procedure?

[Verwijderd]

Op vrijdag 03 mei 2002 12:31 schreef JamesTiberius het volgende:
[..]
Punt 2:
- De database is 2GB groot, temptabellen zijn dus geen optie De omvang van de database is ook de reden voor het gebruik maken van een stored procedure. Ik kan deze functie namelijk wel uitvoeren in ASP (waar ik normaalgesproken mee werk) maar dan is hij een klein weekje bezig ben ik bang .

Een cursor maakt ook een temptable aan, dus dat maakt geen verschil. Dat de database 2GB groot is ook niet overigens, zolangje maar genoeg ruimte hebt in de tempdb. De complete lijst van velden en tables zal niet zo groot zijn (als in: honderden mb's ).

- Je zegt dat die "sp_executesql extended stored procedure" waarschijnlijk geen goede oplossing is. Weet je toevallig geen andere manier om variabel query's samen te stellen en uit te voeren ??

Nee ik doelde op het gebruik van een cursor icm sp_executesql. sp_executesql is de routine die je nodig hebt om custom queries in T-SQL uit te voeren. Het nadeel is dat je veel van je snelheid verliest, want die queries moeten elke keer worden opgebouwd en gecompileerd. Je zou meerdere fields in 1 table kunnen combineren tot 1 UPDATE statement.

Heb je al gekeken hoeveel tables en hoeveel fields het betreft? Want als het 5 tables met ieders 5 velden betreft is het met de hand intikken waarschijnlijk sneller (icm query analyzer)

[Verwijderd]

Deze query levert alle info op die je nodig hebt:

code:

SELECT 
    INFORMATION_SCHEMA.TABLES.TABLE_NAME,
    INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
    INFORMATION_SCHEMA.COLUMNS.DATA_TYPE    
FROM 
    INFORMATION_SCHEMA.TABLES LEFT JOIN INFORMATION_SCHEMA.COLUMNS 
    ON 
        INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE 
    INFORMATION_SCHEMA.TABLES.TABLE_TYPE='BASE TABLE' AND 
    INFORMATION_SCHEMA.TABLES.TABLE_NAME != 'dtproperties'
    AND
    (
        INFORMATION_SCHEMA.COLUMNS.DATA_TYPE='varchar' OR
        INFORMATION_SCHEMA.COLUMNS.DATA_TYPE='nvarchar' OR
        INFORMATION_SCHEMA.COLUMNS.DATA_TYPE='char' OR
        INFORMATION_SCHEMA.COLUMNS.DATA_TYPE='nchar'
    )

(zie de laatste AND clause voor types van velden die jij moet resetten naar 'NULL')

Deze SELECT kun je nu inserten in een temptable, en dan per row die aflopen en daarmee een UPDATE query aanmaken door stringconcatenatie. Die UPDATE query voorzie je van een WHERE clause die test op ''.

[JamesTiberius]

Kewl !! (om het zo maar te noemen) Ik ga het meteen uitproberen.

Het gaat hier trouwens over 471 tabellen met in totaal 14514 velden

Dus handmatig intypen lijkt mij niet zo verstandig