System tables and Dynamic SQL - 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)

System tables and Dynamic SQL

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-12-21 : 10:18:39

I have a table that constantly be updated ( both data and column headers)

Create table test ( A varchar(10), B numeric(12), C char(20) )

Insert into test (‘1001’, ‘123.00’ , ‘Patient’)
go
Insert into test (‘2349’, ‘ ’ , ‘Doctor’)
go
Insert into test (‘7896’, ‘14587.00’ , ‘Worker’)
go
Insert into test (‘5367’, ‘5623.00’, ‘ . ’)
go
Insert into test ( ‘7658’,‘ ‘, ‘Patient’)
go
Insert into test (‘1524’, ’97.00’, ‘.’)

The table looks like :


      A                           B                          C         
     1001                      123.00                      Patient            
     2349                                                  Doctor            
     7896                      14587.00                    Worker            
     5367                      5623.00                       .
     7658                                                  Patient
     1524                      97.00                         .

I would like to find spaces and ‘.’ characters in the table and update them to ‘NULL’. After update, I ‘d like to convert the data type of every columns to VARCHAR

How can I do this by using system tables in dynamic SQL?

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-12-21 : 12:35:26

Here is what I've got so far.. How can I write some dynamic update and select statement that will convert spaces and '.' to 'NULL'?

CREATE PROCEDURE dbo.usp_SASTEST
@TableName varchar(100)
as

Set NOCOUNT ON
Set CONCAT_NULL_YIELDS_NULL OFF

declare @ColName varchar(100)
declare @ColOrder int
declare @Query varchar(5000)

Select @Query = ''

declare cc cursor
fast_forward
for
Select [name]
From SysColumns
Where id in
(Select id
From SysObjects
Where name = @TableName And Type = 'U')
Order By ColOrder
Open cc
Fetch Next From cc Into @ColName
While @@FETCH_STATUS = 0

Begin

--- I need to put some dynamic SQL in here......

Update .....

End
Close cc
Deallocate cc

Print @Query
Set CONCAT_NULL_YIELDS_NULL ON

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-21 : 14:22:47

Why would you want to do this?

HTH

=================================================================

The best things in life are nearest: Breath in your nostrils, light in your eyes, flowers at your feet, duties at your hand, the path of right just before you. -Robert Louis Stevenson, novelist, essayist, and poet
(1850-1894)

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-12-21 : 14:42:01

I want to be able to dynamically update the fields with spaces or '.' in @table. Do you have a better idea?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-21 : 16:32:36

select NullIf(MyColumn, '')
select NullIf(MyColumn, '.')
select NullIf(NullIf(MyColumn, '.'), '')

Refer to Books Online for details.

Good Luck!

HTH

=================================================================

Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-21 : 16:36:34

Why do you think you need dynamic SQL and the system tables for this?

Tara

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-12-21 : 16:50:16

Here is what I got...

create PROCEDURE dbo.usp_missing value
@TableName varchar(100)
as

Set NOCOUNT ON
Set CONCAT_NULL_YIELDS_NULL OFF

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 = @TableName 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

SQLMike
Starting Member

7 Posts

Posted - 2004-12-21 : 17:20:03

At a minimum I would combine your two UPDATE queries into one so it only has to go through the data once:

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources