Database

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

CREATE PROCEDURE dbo.SearchForNull
@columnName sysname
AS
DECLARE @dynamicSql nvarchar(4000);

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_NAME = 'MyTable'
              AND COLUMN_NAME = @columnName)
BEGIN
    -- The column does not exist
    RETURN;
END

SET @dynamicSql = '"SELECT id FROM MyTable WHERE ['+@columnName+'] IS NULL';
EXEC(@dynamicSql);
GO

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

CREATE PROCEDURE dbo.GetEmptyColumnNames
@id int
AS
SELECT 'Sex' AS ColumnName 
FROM MyTable 
WHERE (Sex IS NULL OR Sex='..') AND id = @id
UNION
SELECT 'Subject1' AS ColumnName 
FROM MyTable 
WHERE (Subject1 IS NULL OR Subject1='..') AND id = @id
UNION
SELECT 'Subject2' AS ColumnName 
FROM MyTable 
WHERE (Subject2 IS NULL OR Subject2='..') AND id = @id
UNION -- And so on for all the columns in your table that you want to check.
GO

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

DROP PROCEDURE dbo.GetEmptyColumnNames
 
CREATE PROCEDURE dbo.GetEmptyColumnNames
@id numeric
AS
Select 'a1' as ColumnName
FROM hhdata
WHERE (a1 IS NULL Or a1='..') AND refno = @id
UNION
Select 'a2' as ColumnName
FROM hhdata
WHERE (a2 IS NULL Or a2='..') AND refno = @id
UNION
Select 'a3' as ColumnName
FROM hhdata
WHERE (a3 IS NULL Or a3='.') AND refno = @id