Solved

how to loop through all the columns in a table

Posted on 2008-10-08
17
6,304 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 …
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

810 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