Solved

how to loop through all the columns in a table

Posted on 2008-10-08
17
6,241 Views
Last Modified: 2011-09-20
Here is my scenario:

I have a table with around 100 columns. I want to loop through all the columns and extract all the records where 10 of the columns are NOT empty/null and the other 90 ARE empty/null.

How do I write a stored procedure to do this? By the way, if it matters, I am still on SQL 2000.
0
Comment
Question by:IUAATech
  • 8
  • 7
  • 2
17 Comments
 
LVL 13

Expert Comment

by:sm394
ID: 22670036
Try the attached code snippet let me know if u need any explanation

IF  object_id(N'tempdb..[#tmp]') is not null 

	DROP TABLE #tmp

-----------Load the result set in to temp table with Row Number------

SELECT 

IDENTITY (INT,1,1) AS RowNo      

,[PK]+0 AS  [ID]

,[Field]

INTO #tmp    

FROM [table]

--****************************************************

DECLARE	@PK INT

DECLARE @Field INT

--************************************************************

DECLARE @RowCount int

 

SELECT @RowCount =COUNT(*) FROM #tmp

--

declare @counter integer

set @counter = 1

---------Loop and get single row at a time----

while @counter <= @RowCount

begin

------------------------------	

SELECT 

 @PK=PK

 @Field=Field

 FROM #tmp

 WHERE  RowNo=@counter   

----------------------

--PERFORM YOUR IF LOGIC HERE I.E.

IF (@Field=1)

Begin

-- Code block if true

END

----------------Move to next row------------------------------------

set @counter = @counter + 1

 

end --end while 

Open in new window

0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22670056
here is how i made it using dynamic SQL:




declare @tableName varchar(100)

declare @nullFieldsCountLimit int

declare @tableFieldId varchar(100)
 

set @tableName = 'yourTableName'

set @tableFieldId = 'yourTableFieldId'

set @nullFieldsCountLimit = 5 -- give me only records that have 5 or more null values
 
 
 

------------- no need to edit from here -----------------------
 

declare @sql nvarchar(4000)

declare @fields nvarchar(4000)
 
 

Set @sql = 'Select ' + @tableFieldId + ', NullFieldsCount = '

set @fields = ''

select 

	@fields = @fields + ' case when ' + syscolumns.[name] + ' is null then 1 else 0 end + ' + char(10) + char(13)

from sysobjects inner join syscolumns on sysobjects.id = syscolumns.id

where sysobjects.[name] = @tableName 
 

-- remove last +

set @fields = lefT( @fields, len(@fields) - 4) + char(10) + char(13)
 

set @sql = @sql + @fields + 'from ' + @tableName +  char(10) + char(13) + ' where ' + @fields + ' >= ' + cast (@nullFieldsCountLimit as varchar(10) )
 

print @sql
 

exec sp_executesql @sql

Open in new window

0
 

Author Comment

by:IUAATech
ID: 22670318
We are getting there.

sm394,
how does [PK]+0 work? What's the '+0' for?

Cvijo123,
looks like the length of @sql ends up being more than 4000. How do I deal with that?

one last point, will it help if we could declare the list of ten fields somewhere in the code?
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22670555
ok i edit it little, so now u will have table in your database called "tmpTable_deleteWhenDone"

so u can use select against that table when u need and drop it after u are done.
it can be done without that table too but i think u will need it to later join your results with real table.

SQL:



declare @tableName varchar(100)

declare @nullFieldsCountLimit int

declare @tableFieldId varchar(100)

 

set @tableName = 'yourTableName'

set @tableFieldId = 'yourTableFieldNameID' -- should be your unique key field

set @nullFieldsCountLimit = 5 -- give me only records that have 5 or more null values

 

 

 

------------- no need to edit from here -----------------------

 

declare @sql varchar(8000)

declare @fields varchar(8000)
 

if( exists( Select * from sysobjects where sysobjects.id = object_id('tmpTable_deleteWhenDone' ) ) )

begin

	drop table tmpTable_deleteWhenDone

end

 

 

Set @sql = ' Select ' + @tableFieldId + ', NullFieldsCount into tmpTable_deleteWhenDone from  ( Select ' + @tableFieldId + ', NullFieldsCount = '

set @fields = ''

select 

        @fields = @fields + ' case when ' + syscolumns.[name] + ' is null then 1 else 0 end + ' + char(10) + char(13)

from sysobjects inner join syscolumns on sysobjects.id = syscolumns.id

where sysobjects.[name] = @tableName 

 

-- remove last +

set @fields = lefT( @fields, len(@fields) - 4) + char(10) + char(13)

 
 

 

set @sql = @sql + @fields + ' from ' + @tableName + ' ) tmp where tmp.NullFieldsCount >= ' + cast ( @nullFieldsCountLimit as varchar(10) )
 

print @sql

exec ( @sql )
 
 

Select * from tmpTable_deleteWhenDone

Open in new window

0
 

Author Comment

by:IUAATech
ID: 22670732
Cvijo123, I ran your query after changing the tablename and the tablefieldid and I got the following error message when using SQL Server Management Studio.

Msg 156, Level 15, State 1, Line 75
Incorrect syntax near the keyword 'from'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tmpTable_deleteWhenDone'.

One more question for you: Where in your code can I specify which fields should be null/empty? Let's say out of the 100 fields, I have the following fields that shouldn't be null:
email
first_name
last_name
address1...etc. You get the idea.
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22670820
- question 1:
can u commnet last 2 lines in my code

-- exec ( @sql )
 -- Select * from tmpTable_deleteWhenDone

and past here messege that u should get in your SQL managment studio

- question 2:
mybe i didnt get you right but my code will return all rows in your table that have more than @nullFieldsCountLimit filed that are null (in my example all records that have 5 and more fields as nulls )

So u want to enter fields that i need to exclude in my code if i get u right ( like 10 fields i shouldnt look ? )  or u wanted to get only from 10 of those fields values that are null ?

if u will sepcify those columns that need to be checked than we dont need to go to sysColumns and our query should be simple.

Mybe can u give me example of 2+ rows and explain what u should get as results?
0
 

Author Comment

by:IUAATech
ID: 22670971
I will try amd make this simple and provide a scenario for you:

I have a table called "UpdatedRecords" and it contains information ranging from home/business address, e-mail, phone, alumni degree, major/minor, division and lots of other fields.

For now, I want to extract a list of records that have only e-mail and home address updates. So, I look in the e-mail address field, address1/address2/city/state/zip fields and make sure they are not null/empty.

However, at the same time, I have to make sure that none of the other fields (degree, major/minor etc) contain any data (i.e. are null/empty.).

We have a script that will batch the email and the home addresses updates separately. All I am trying to do is generate a csv file for the script that has e-mail and home address elements.

Hope this makes sense.

Thanks.

0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22671124
if i get u right this time

You need to give fields that cannot containt nulls (if any of those contain null/empty it should be excluded)
and all other fields must be null/empty ?

example:
u send e-mail, state as those field so query need to give u results for all the records that have fill e-mail and state fields and all other fields must be null ?

and if u have any unique key for that "updateRecords" table (identity field) can u give me the name of it ?

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:IUAATech
ID: 22671192
You almost have it correct.

Let's start with the fields. Let's say I want to extract home address updates. So the fields I will have to check for are address1, address2, address3, city, state, zip, country.

I want to make sure that all the other fields are null/empty except for the ones I have listed above. The ones I have listed above could still be empty/null (since address3 is not applicable for most address). However, all the OTHER fields should definitely be null/empty.

And the primary key is "UpdateID".
0
 

Author Comment

by:IUAATech
ID: 22671204
One more thing I could like to add, if I may: The table has a field named "void" which is of type bit. I would like to set this to 1 for each record that I extract.

Thanks again.
0
 
LVL 13

Expert Comment

by:sm394
ID: 22671513
Since we are loading our columns in to Temp table while adding new field IDENTITY as Row No and i assume you have a Primary key on your table as well TSQL Doesn't allow this so
[PK]+0 is just a work around for that
0
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 500 total points
ID: 22671744
ok i changed my code fully doing it other way now.

First u need to add this function to your SQL database


create function dbo.udfSplitStringToTable
(
      @string varchar(8000),
      @delimiter varchar(5)
)

RETURNS @tt1 table (columnName varchar(50) )
AS
BEGIN
      declare
            @columnName varchar(50),
            @brojac int
            
      SET @brojac = 0
      IF RIGHT(@string, 1) <> @delimiter
            SET @string = @string + @delimiter
      
      While len( @string ) > 1 and @brojac < 25
      BEGIN
            set @columnName = left(@string, PATINDEX( '%' + @delimiter + '%', @string ) -1      )
            set @string = substring( @string, PATINDEX( '%' + @delimiter + '%', @string ) + 1, 4000 )
            set @brojac = @brojac + 1
            insert into @tt1 (columnName) values (@columnName)
            
      END
      
      return
END




and then execute this query (change field u need before)
if u get some error i would like to see message that i print out by commenting line exec (@sql) with --exec (@sql)
then u should get query printed in your message window and paste it here if it is not problem

Are all your fields string types (char, varchar...) if not what should be empty value for number, decimals, int types (always <null> or mybe even 0 )

declare @tableName varchar(100)

declare @tableFieldId varchar(100)

declare @tableFieldsToExtract varchar(500)

 
 

set @tableName = 'UpdatedRecords'

set @tableFieldId = 'UpdateID'

set @tableFieldsToExtract = 'address1#address2#city#state#'
 
 

 

------------- no need to edit from here -----------------------
 

declare @columns table ( columnName varchar(100), exclude bit default 0 )
 

insert into @columns

select 

       syscolumns.[name],

			 case when col.columnName is null then 0 else 1 end

from sysobjects 

	inner join syscolumns on sysobjects.id = syscolumns.id

	left join ( Select * from dbo.udfSplitStringToTable(@tableFieldsToExtract, '#') ) col

		on col.columnName = syscolumns.[name]

where sysobjects.[name] = @tableName 
 
 
 

declare @sql varchar(8000)

declare @Select varchar(8000)

declare @where varchar(8000)
 

Set @where = ' where '

Set @Select = ' Select ' + @tableFieldId + ', ' +char(10)
 

select 

      @Select = @Select + case when col.exclude = 1 then col.columnName + ', ' + char(10) else '' end

from @columns col
 

-- remove last ,

set @Select = lefT( @Select, len(@Select) - 3) + ' ' + char(10)
 

set @Select = @Select + ' from ' + @tableName + ' ' + char(10)
 

select 

      @where = @where + case when col.exclude = 0 then '( ' + col.columnName + ' is null or ' + col.columnName + ' = '''' ) and ' + char(10) else '' end 

from @columns col
 

-- remove last and

set @where = lefT( @where, len(@where) - 5) + ' ' + char(10)
 
 

set @sql = @Select + @where
 

print @sql
 

exec (@sql)

Open in new window

0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22671936
dont forget u need to add your ID field your tableFieldsToExtract  since id field will never be null
0
 

Author Comment

by:IUAATech
ID: 22672129
The script runs without any errors. However, it looks like UpdateID (the tableFieldId) is being included as part of the "is null or = ' ' clause.

I see the following code:
( UpdateId is null or UpdateId = '' ) and .....
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22672151
read my comment before your post :) .. include UpdateID in your tableFieldsToExtract  fields
0
 

Author Comment

by:IUAATech
ID: 22672156
ah yes :-)
0
 

Author Comment

by:IUAATech
ID: 22672169
this is prefect. Thank you so much.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now