Solved

how to loop through all the columns in a table

Posted on 2008-10-08
17
6,178 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
- 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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:IUAATech
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
dont forget u need to add your ID field your tableFieldsToExtract  since id field will never be null
0
 

Author Comment

by:IUAATech
Comment Utility
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
Comment Utility
read my comment before your post :) .. include UpdateID in your tableFieldsToExtract  fields
0
 

Author Comment

by:IUAATech
Comment Utility
ah yes :-)
0
 

Author Comment

by:IUAATech
Comment Utility
this is prefect. Thank you so much.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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

16 Experts available now in Live!

Get 1:1 Help Now