Script help.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Script help....

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-21 : 11:26:28

Hi I am trying to write a loop so that the all the blank fields in the supplier table are counted and recorded in another table:

create table Table1
(column_Name varchar(150),
Blank_Quantity Numeric)

declare @SqlString varchar(8000),
@Column_name varchar(150),
@Blank_Quantity numeric,
@Counter numeric,
@Max numeric

set @Max = (select count(*) from information_schema.columns where table_name = 'SUPPLIER_TABLE')
set @Counter = 1

while @Counter <= @Max
Begin
set @Column_name = (select Column_name from information_schema.columns where table_name = 'Supplier_table' and Ordinal_position = @Counter)
set @Blank_Quantity = (select sum(case when (@Column_name) is not null and (@Column_name) <> '' then 0 else 1 end) from Supplier_Table)
insert into Table1 values(@Column_name, @Blank_Quantity)
set @Counter = @Counter + 1
End

Intended output inserted into Table:

Field Blank Records
Supplier_No 100
Supplier 0
Address_1 0
Postcode 7

At the moment my script only returns the value 0 for the variable @Blank_Quantity. Do you know any reason why???

Thanks in advance!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-02-21 : 13:54:56

maybe something like


select 	sum(case Supplier_No when null then 1 else 0 end ) as Supplier_No_Count
	,sum(case Supplier when null then 1 else 0 end ) as Supplier_Count
	,sum(case Address_1 when null then 1 else 0 end ) as Address_1_Count
	,sum(case Postcode when null then 1 else 0 end ) as Postcode_Count
from
	Supplier_Table

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 14:16:52

"sum(case Supplier_No when null then 1 else 0 end ) "

COUNT(Supplier_No) will do the same job, possibly quicker - but you will have to, potentially, deal with the warning message generated because of any NULL values that are excluded (SUM, as you wrote it, will not generate any such warning)

Kristen

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-02-22 : 14:58:29

hence my caveat "maybe something like"

Kristen
Test

22859 Posts

Posted - 2007-02-22 : 15:01:03

hence my caveat "but you will have to, potentially, deal with ..."

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-02-23 : 13:42:52

jeanierulio, ergo sum

{I geenie rule, therefore I am}

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Kristen
Test

22859 Posts

Posted - 2007-02-24 : 03:54:12

caveat emptor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources