Parse Table for Nulls by Column 1

[hwkranger]

I write this, and it works. It pulls the column names along with the Primary Key where rows have a null in the given column.

example out put:

Loan_Num, NullColumnName
123123 LN_Setup_date
123121 LN_Setup_Date
123121 CR_IND_SW
...............

also outputs in another format when I change the bit

Loan_num, NullColumnName
123123 LN_Setup_Date
123121 LN_Setup_Date, CR_IND_SW
..................

This is used to find data that users input incorrectly to the Mainframe. The SQL server is just a copy of the Mainframe data.

I would like this to work across multiple databases. Since I use the INFO_SCHEMA view the sp has to be created in all databases where it is to be run.

Can someone help me? I'd like this to be a utliilty that runs across multiple databases... I'm not sure how to procede.

/*
CREATE PROCEDURE dbo.sp_FetchNullColumnNames
(
 @TABLENAME as varchar(100),
 @MULTIRECORD as BIT, -- Determins if you use a new record for each Column Null, or Concatonate it
 @varCOLUMN_NAMES as VARCHAR(2000)
)

AS
*/

DECLARE @TABLENAME as varchar(100)
DECLARE @MULTIRECORD as BIT -- Determins if you use a new record for each Column Null, or Concatonate it
DECLARE @varCOLUMN_NAMES as VARCHAR(2000)
DECLARE @DATABASE as varchar(40)

-- TEST VALUES
SET @MULTIRECORD = 0  -- THIS MUST BE SET, 1 = NEW ROW, 0 = UPDATE EXISTING ROW
SET @TableName = 'Master1' -- THIS MUST BE SET
SET @varCOLUMN_NAMES = 'ln_SetUp_Date, CR_IND_SW'

-- INTERNAL HANDLED VARIABLES
DECLARE @SQL as varchar(2000) -- SQL THAT IS USED TO EXECUTE INTO THE TABLES
DECLARE @t Table (Column_name varchar(100)) -- TABLE FOR HOLDING COLUMN NAMES BUILT FROM SCHEMA
DECLARE @ColumnName as varchar(100) -- SINGLE COLUMN NAME
DECLARE @tblCOLUMN_NAMES TABLE(Column_name varchar(100)) -- TABLE BUILT FROM STRING OF COLUMNS PASSED

-- PARSE THE LIST INTO A TABLE
SELECT @varCOLUMN_NAMES = @varCOLUMN_NAMES + ','

IF CHARINDEX(',', @varCOLUMN_NAMES) > 0 BEGIN
     WHILE CHARINDEX(',',@varCOLUMN_NAMES) > 0 BEGIN
	INSERT @tblCOLUMN_NAMES VALUES(LTRIM(RTRIM(SUBSTRING(@varCOLUMN_NAMES, 1, CHARINDEX(',', @varCOLUMN_NAMES) - 1))))
	SELECT @varCOLUMN_NAMES = STUFF(@varCOLUMN_NAMES, 1, CHARINDEX(',', @varCOLUMN_NAMES),'')
     END
END
--SELECT * FROM @tblCOLUMN_NAMES

Insert into @t
SELECT COLUMN_NAME
 FROM
 INFORMATION_SCHEMA.COLUMNS 
Where
 Table_Name = @TableName
  AND
 COLUMN_NAME in (SELECT Column_name From @tblCOLUMN_NAMES)

-- THIS DOES NOT REALLY WORK --
If object_ID('#L') is not null
	begin
		drop table #L
	end
-- END OF NON-WORKING SECTION --

Create Table #L (Loan_Num char(10), NullColumnName varchar(2000))
while exists(Select * from @t) -- This pulls from our temp table of columns
	begin
		set @COLUMNNAME = (Select top 1 Column_Name from @t)

		IF @MULTIRECORD = 0
			BEGIN	
			--select @ColumnName
			-- For multiple columns
			Set @SQL = ''
			SET @SQL = @SQL + ' Update #L Set NullColumnName = NullColumnName + '',  ' + @ColumnName + '''' 
			SET @SQL = @SQL + ' FROM #L INNER JOIN ' + @TableName + ' T ON #L.Loan_Num = T.Loan_Num'
			exec (@SQL)
			--select (@SQL)
	
			-- Add the ones missing
			set @SQL = ''
			set @SQL = @SQL + ' Insert into #L (Loan_Num, NullColumnName)'
			set @SQL = @SQL + ' SELECT T.Loan_Num, ' + '''' + @ColumnName + ''''
			set @SQL = @SQL + ' FROM ' + @TableName + ' T Left Join #L '
			set @SQL = @SQL + '    ON T.Loan_Num = #L.Loan_Num'
		 	set @SQL = @SQL + ' WHERE ' + @ColumnName + ' is NULL and #L.Loan_Num is null'
			exec (@SQL)
			--select (@SQL)
			END
		IF @MULTIRECORD = 1
			BEGIN
				set @SQL = ''
				set @SQL = @SQL + ' Insert into #L (Loan_Num, NullColumnName)'
				set @SQL = @SQL + ' SELECT Loan_Num, ' + '''' + @ColumnName + ''''
				set @SQL = @SQL + ' FROM ' + @TableName 
			 	set @SQL = @SQL + ' WHERE ' + @ColumnName + ' is NULL'
				exec (@SQL)
			END

		Delete from @t where Column_Name = @ColumnName
	end

select * from #L -- return data from our temp loans table

-- Clean up
drop table #L
--drop table #t

Randall Vollen
Merrill Lynch

 

[AlexCuse]

I am pretty sure that you can create it in Master DB (which is first place SQL will look for any stored proc with the sp_ prefix anyway if memory serves), and then it will run in the context of the session it is run from (provided you are not specifying database anywhere in there, I did not see any)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[hwkranger]

I was hoping to avoid putting this in Master, but if that's the way to go... then I'll go with it.

Randall Vollen
Merrill Lynch

 

[AlexCuse]

Sorry, I think it's basically either put it in master or put it in every database. If you aren't putting something in master, I would recommend you avoid using the sp_ prefix, as this is reserved for system stored procedures.

So if you use sp_, even if the proc is in your current database, SQL will look first in system stored procedures stored in master, and then in the current DB. Just a thought.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[hwkranger]

Thank you very much Alex.



Randall Vollen
Merrill Lynch