CODEWITHSUNDEEP
sqlsql-servert-sql
ghoston3rd
ghoston3rd

Reputation: 149

SQL Server stored procedure/cursor

I have a list of 20 spatial tables (Zoom1-Zoom20) and from time invalid geometry pops up in these tables. When the invalid geometry occurs I run the following statement to find where the invalid geometry is:

SELECT ID FROM Zoom10 WhERE Location.STIsValid() = 0

Typically I have to run the above statement for every Zoom table (the error that leads to the invalid geometry does not indicate which zoom table has invalid geometry) and when a result is returned from the select statement I run the following statement to correct the geometry:

UPDATE MGeoZoom10 set Location = Location.MakeValid() where Location.STIsValid() = 0

My question is can this process be automated with a stored procedure that gets the list of zoom tables

select name from sys.tables where name like '%zoom'

and then loops through the zoom tables with 

SELECT ID FROM Zoom10 WhERE Location.STIsValid() = 0

and if a result is returned it runs the update statement on the zoom table?

Upvotes: 0

Views: 117

Answers (2)

Ash8087
Ash8087

Reputation: 701

Do you have 2 UDFs called STIsValid and MakeValid? If so, you could do something like this...

    SELECT id INTO #Processed FROM Sysobjects WHERE name = '(no such table)'

    DECLARE @TableId int, @TableName varchar(255), @CorrectionSQL varchar(255)

    SELECT @TableId = MIN(id) FROM Sysobjects WHERE type = 'U' AND name LIKE '%zoom'
    AND id NOT IN (SELECT id FROM #Processed)

    SET @TableId = ISNULL(@TableId, -1)

    WHILE @TableId > -1 BEGIN

        PRINT @TableId

        SELECT @TableName = name FROM Sysobjects WHERE type = 'U' AND id = @TableId
        SET @CorrectionSQL = 'UPDATE ' + @TableName + ' SET Location = dbo.MakeValid(Location) where dbo.STIsValid(Location) = 0'

        PRINT @CorrectionSQL
        EXEC(@CorrectionSQL)


        INSERT INTO #Processed (id) VALUES(@TableId)

        SELECT @TableId = MIN(id) FROM Sysobjects WHERE type = 'U' AND name IN ('DimAccount', 'DimCurrency', 'DimCustomer')
            AND id NOT IN (SELECT id FROM #Processed)

    END

Upvotes: 0

George Mastros
George Mastros

Reputation: 24498

Try this:

sp_msforeachtable '
if ''?'' Like ''%Zoom%'' 
    Begin
        If Exists(SELECT ID FROM ? WhERE Location.STIsValid() = 0)
            UPDATE ? set Location = Location.MakeValid() where Location.STIsValid() = 0
    End     
'

Upvotes: 1

Related Questions