Help in script....variable returns 0....
-
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-2 numeric
set @max-2 = (select count(*) from information_schema.columns where table_name = 'SUPPLIER_TABLE')
set @Counter = 1
while @Counter <= @max-2
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!
-
your issue in the script is that the blank quantity var will only either be 1 or 0 and that is based upon the @column_name being null or <> '', which would never happen so it would always be 0.
i think what you want to do is write something dynamic that will move through all of the columns in your table and give a count of where it is null or = ''. you would need to put together a dynamic string and then execute the string to store the values into the table you have setup, which would be included in the dynamic sql.
here is how the dynamic sql would be put together:
'insert into table1(column_name, blank_quantity) select ' + @column_name + ', count(*) from supplier_table where ' + @column_name + ' is null or ltrim(rtrim(' + @column_name + ')) = '''''
you could build a cursor that loops through the column list, which then sets up your @column_name var. in the loop, you execute the sql statement put together in the string via an exec(@sql)
hope this gets you started.
-
You will need to also check the column type, as you can't expect to find empty strings in numeric fields, nor would SQL Server be happy if your code looked for these
.An alternative to a cursor approach is below - not really a performance issue, but just because "alternatives to cursors" are good skills to develop.
CREATE TABLE #ResultsTable1
(ColumnName VARCHAR(150)
,BlankQuantity INT)
DECLARE @SqlString varchar(500)
,@TableName varchar(150)
,@ColumnName varchar(150)
,@ColumnType tinyint
,@OrdinalPosition int
SET @TableName = 'Supplier_table'
SET @OrdinalPosition = 1
WHILE @OrdinalPosition > 0
BEGIN
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @OrdinalPosition)
BEGIN
SET @ColumnName = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @OrdinalPosition)
SET @ColumnType = (SELECT CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL
THEN 1 -- numeric/date/bit
ELSE 2 END -- char/varchar/text
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @OrdinalPosition)
IF @ColumnType = 1
BEGIN
SET @SqlString =
'SELECT '''+@ColumnName+''',COUNT(*) FROM ['+@TableName+'] '+
'WHERE ['+@ColumnName+'] IS NULL '
END
IF @ColumnType = 2
BEGIN
SET @SqlString =
'SELECT '''+@ColumnName+''',COUNT(*) FROM ['+@TableName+'] '+
'WHERE ISNULL(['+@ColumnName+'],'''') = '''' '
END
INSERT INTO #ResultsTable1(ColumnName,BlankQuantity)
EXEC (@SqlString)
SET @OrdinalPosition = @OrdinalPosition + 1
END
ELSE
SET @OrdinalPosition = 0
END
-
Thanks for those suggestions guys.
Chuck Rivel, I do not understand why the Blank_Quantity will either be a 1 or 0?
If I hard-code the statement:
select sum(case when Column1 is not null and Column1 <> '' then 0 else 1 end) as Blank_records from Supplier_Table
Then this will return my desired results. All I need to do is try and represent the column names with the variable @Column_Name.
The purpose of it is to count the blank records for that field.
I tend to avoid any use of cursors due to performance issues.
-
what sql is reading is the literal value of @column_name and checking to see if that is null or '' and i take it that is not what you want.
you want to piece together your sql statement into a string and execute that string value. if you want to stay away for cursors, as it is recommended, try malcom's approach
-
Thanks Chuck and Malcolm that works a treat.
I'm trying to add more columns to the table to audit other criteria, but am having problems thinking of how to adjust the code to add more columns. i.e:
Table:
Table Column_name Blank_Quantity Distinct Min Length Max Length Supplier_Table Supplier_No 0 30000 5 10 Supplier_Table Supplier_name 13 29999 10 50 Is there a way to treak the code and add these extra checks, or would a new table have to be created for each column and then combined....?
Any help very much appreciated to start me off!
Many Thanks!
p.s
distinct check: select count(distinct column_name) from Supplier_table
Min Length : select min(len(column_name)) from Supplier_Table
-
sure, reset your sql string to each of the new queries that you want to execute and then update your temp table with the execution for each piece OR create a single select statement that handles each piece as a different column.
for the single select, you might want to make the dynamic sql come together to look something like this. (this is what the sql string would evaluate to if you printed it before executing):
select a.column_name, a.blank_qty, b.distinct_count
from (select 'column_name' as column_name, count(*) as blank_qty from table where column is null) a
inner join (select 'column_name' as column_name, count(distinct column_name) as distinct_count from table) b
on a.column_name = b.column_name
this should get you started, good luck
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply