Dynamic update - 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)

Dynamic update

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-01-12 : 12:45:24

I am trying to clean up the columns which have either ‘.’ Or ‘ ‘ in Table A and B( or whatever I put into the Table variable (@Table)) . I am trying to dynamically pass the table name in @Table into sysobject table.So,I can write the dynamic SQL What am I doing wrong here? I can’t get the result that I want…

Set NOCOUNT ON
Set CONCAT_NULL_YIELDS_NULL OFF
Declare @Table Table
( Tablename varchar(20) not null)
Insert @Table values( 'A' )
Insert @Table values ('B')

Declare @TableName Varchar(20)

declare @ColName varchar(100)
declare @ColOrder int
declare @Query1 varchar(5000)
declare @Query2 varchar(5000)
declare @Query3 varchar(5000)

Select @Query1 = ''
Select @Query2 = ''
Select @Query3 = ''

declare cc cursor
fast_forward
for
Select [name]
From SysColumns
Where id in
(Select id
From SysObjects
Where
name in ( select Tablename = @TableName from @Table)And Type = 'U')
Order By ColOrder

Open cc
Fetch Next From cc Into @ColName
While @@FETCH_STATUS = 0

Begin

select @Query1 = 'UPDATE ' + @TableName + ' SET ' + @ColName + ' = NULL WHERE ' + @ColName + ' = ''.'''
Execute (@query1)

select @Query2 = 'UPDATE ' + @TableName + ' SET ' + @ColName + ' = NULL WHERE LTRIM(RTRIM(' + @ColName + ')) = '''''
Execute (@query2)

select @Query3 = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColName + ' varchar(50)'
Execute (@query3)

Fetch Next From cc Into @ColName
End
Close cc
Deallocate cc
Print(@query1)
Print(@query2)
Print(@query3)
Set CONCAT_NULL_YIELDS_NULL ON
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-12 : 12:54:34

...From SysObjects
Where
name in ( select Tablename = @TableName from @Table)And Type = 'U')
Order By ColOrder
...

maybe if you change it to this:
select TableName from @Table where Tablename = @TableName

Go with the flow & have fun! Else fight the flow

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-01-12 : 13:05:09

Sorry , but it didn't work.

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-12 : 14:47:06

Well it's probably good it doesn't work.

This kind of thing can really mess stuff up.

I mean, I would you the catalog to generate the scripts...save them in a *.sql file, Restore a database to another database, test the script there, nake sure it all works ok, the execute the script using osql from a bat file...

It seems like you're not sure where the stuff is you want to change.

I would identify those problem situation first and localize my changes..


Set NOCOUNT ON
Declare @Table Table
( Tablename varchar(20) not null)
Insert @Table values( 'Orders' )


Declare @TableName sysname, @ColName sysname, @ColOrder int, @Query1 varchar(5000), @Query2 varchar(5000), @Query3 varchar(5000)

declare cc cursor
FOR
    SELECT c.COLUMN_NAME, c.TABLE_NAME
      FROM INFORMATION_SCHEMA.Columns c 
INNER JOIN INFORMATION_SCHEMA.Tables t 
	ON c.TABLE_NAME = t.TABLE_NAME
     WHERE TABLE_TYPE = 'BASE TABLE' 
       AND EXISTS (SELECT * FROM @table i WHERE t.TABLE_NAME = i.TableName)
  ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION

Open cc
Fetch Next From cc Into @ColName, @tablename

While @@FETCH_STATUS = 0
  BEGIN

	select @Query1 = 'UPDATE ' + @TableName + ' SET ' + @ColName + ' = NULL WHERE ' + @ColName + ' = ''.'''
	SELECT @query1
	select @Query2 = 'UPDATE ' + @TableName + ' SET ' + @ColName + ' = NULL WHERE LTRIM(RTRIM(' + @ColName + ')) = '''''
	SELECT @query2
	select @Query3 = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColName + ' varchar(50)'
	SELECT @query3

	Fetch Next From cc Into @ColName, @tablename
  END

CLOSE cc
DEALLOCATE cc
GO

Oh, btw, @tablename was never set so it was always null

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources