July 10, 2003 at 2:56 am
I have a web application which works out for any given row in a table how many of the fields are incomplete (int field 0 or null, nvarchar field null or zero length string). The result is say 6 out of 22 fields are incomplete and a string containing field "numbers" indicating the incomplete fields. I do this using functions in asp.
Is there a simple way in T-SQL to work out for all the fields in all the rows if they are incomplete - as defined above, and come up with either the incomplete field names or a count of the incomplete fields in each row
Appreciate any help
David
July 10, 2003 at 5:30 am
Hi HallD
you could try using substring to return the column name as follows :-
create table incomplete_test
(col1 int , col2 varchar(10))
insert into incomplete_test
values (0, "A")
insert into incomplete_test
values (0, "")
insert into incomplete_test
values (0, null)
insert into incomplete_test
values (1, "A")
insert into incomplete_test
values (1, "")
insert into incomplete_test
values (1, null)
and then use the following select (replacing col1 & col2 with your own column name):-
select substring('col1', 10 *col1 , 10) + " " + substring('col2', 10*len(isnull(col2,"")), 10) from incomplete_test
all int columns need to be handled by substring('col1', 10 *col1 , 10)
all varchar columns need to be handled by
substring('col2', 10*len(isnull(col2,"")), 10)
it's a bit crude, but it'll get you started
Paul
July 10, 2003 at 6:53 am
If the number of columns and column names is not too large then you could try this
declare @tablename varchar(50)
set @tablename = 'tablename'
declare @sql nvarchar(4000)
declare @colct int
set @colct=0
set @sql = 'select '
select @sql = @sql + (case
when xtype IN (56,108) then '(case when isnull('+name+',0)=0 then 1 else 0 end)+'
when xtype IN (167,175,231) then '(case when isnull(len('+name+'),0)=0 then 1 else 0 end)+'
when xtype=61 then '(case when '+name+' is null then 1 else 0 end)+'
else '(case when isnull(len(cast(x as varchar)),0)=0 then 1 else 0 end)+' end),
@colct=@colct+1
from syscolumns where id = object_id(@tablename)
set @sql = @sql + '+0 as incomplete,'+cast(@colct as varchar)+' as outof from ' + @tablename
exec sp_executesql @sql
It will get you started and can be tailored to suit your needs.
Far away is close at hand in the images of elsewhere.
Anon.
July 10, 2003 at 8:45 am
Paul and David
Many thanks I'll try them and see where they go!!!
David
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply