November 14, 2004 at 3:29 am
hi all;
will anybody help on solving this problem; i can use all help i can get.
one single table with many records and about 70 fields, each record has unique CustomerID and other fields that can be empty, null, int, etc..
for each customer (single record) i would like to tell him what fields are completed (with data) and what fields not-completed-yet (without data).
null, blank, or zero in any field means not-completed.
the Stored-Procedure should only read one-single record with 70 fields; loop through all 70 fields and return the total-count that represent the nulls+blanks+zeros; just one single number for this specific CustomerID.
please help in writing this Stored-Procedure.
thanks all in advance.
best regards;
November 14, 2004 at 4:26 pm
This sounds a bit suspect to me. Can you provide a bit more detail about what you are going to do with the number? If I was filling in a form, I would not want to receive a message stating that I had "failed to complete 17 items of information - please re-enter" or something similar.
Is this a web app? If so, you would probably be better off performing client-side validation here.
Cheers
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 14, 2004 at 4:42 pm
hi;
yes; its a massive huge web protal; ecah customer can fill-up many forms (more than 50) where each form data are saved in particural table in the database.
at the fill-up time; the customer may or may not have all the data, but he or she can later (anytime in future) generate a report that shows % of completion for each form, so for each Customer the stored procedure will recive the CustomerID then loop through all of the 50 tables and calculate the sum of nulls+blanks+empty fields (columns).
the % of completion has awarding packages for the customers with higher %.
i never ever faced any problems in programming stored-procedure but this on consumed more than a week now, and i feel that i lost the track.
any help or direction will fully appreciated.
November 14, 2004 at 6:52 pm
Yuk. I'm assuming that you have tried (or perhaps you do not want to try!) performing a SELECT of all the fields into local variables and then checking them one by one (ie completely hard coding the test for each field) - and counting along the way? This would be long-winded, but I don't see why it wouldn't work.
I cannot think of a dynamic way to achieve this without writing lots of code, though someone else here probably can.
Regards
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 14, 2004 at 8:03 pm
Try this, it uses function instead of stored procedure. One can modify as needed.
use pubs
go
select * into test from authors
go
create function fn_Count_fields(@au_id as varchar(11))
returns int
as
begin
declare @res int
select @res = sum(
case when au_lname is null or len(rtrim(au_lname)) = 0 then 1 else 0 end +
case when au_fname is null or len(rtrim(au_fname)) = 0 then 1 else 0 end +
case when phone is null or len(rtrim(phone)) = 0 then 1 else 0 end +
case when address is null or len(rtrim(address)) = 0 then 1 else 0 end +
case when city is null or len(rtrim(city)) = 0 then 1 else 0 end +
case when state is null or len(rtrim(state)) = 0 then 1 else 0 end +
case when zip is null or len(rtrim(zip)) = 0 then 1 else 0 end +
case when contract is null or contract= 0 then 1 else 0 end)
from test where au_id = @au_id
return @res
end
GO
-- code generate case in the function, have to modify before use.
select 'case when ' + column_name + ' is null or ' +
case when data_type in ('char', 'varchar') then 'len(rtrim(' + column_name + ')) = 0 then 1 else 0 end'
when data_type in ('int', 'decimal', 'smallint', 'money', 'tinyint') then column_name + '= 0 then 1 else 0 end'
else column_name + '= 0 then 1 else 0 end'
end
from INFORMATION_SCHEMA.columns a
where a.table_name = 'test' and not exists(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c on b.table_name = c.table_name and
b.CONSTRAINT_NAME = c.CONSTRAINT_NAME and b.CONSTRAINT_TYPE = 'PRIMARY KEY' and
a.column_name = c.column_name)
go
update test set address=null, zip=null where au_id = '172-32-1176'
go
select * from test where au_id = '172-32-1176'
go
select dbo.fn_Count_fields('172-32-1176')
go
drop table test
go
November 15, 2004 at 7:17 am
I like wz700's answer.
BUt it sounds like to me an application issue. Before it is entered in, have the app do a check.
Also you may want to consider an extra field on the row to say completed. Filled in at insert time, it would make your queries much shorter.
Have fun.
Eric
November 15, 2004 at 3:52 pm
"Also you may want to consider an extra field on the row to say completed."
Expanding on Eric's idea, why not create an extra calculated field on the table = number of blank/empty fields?
This may start hitting performance (and it may be too complex to code), so perhaps just a standard new int field, maintained by an insert/update trigger - same idea though, the data's there for you in one hit when you need it.
Regards
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 15, 2004 at 8:22 pm
hi all;
first; thanks all for this high quality support, i will try to squeeze my time and help people around here as an appreciation for your support.
back to the issue; i decided to go with the solution of adding the extra field; this solved the issue and resulted in a much faster and cleaner solution. eventhough i don't like calculated-fields in tables.
thanks again and best regards;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply