Ok, 4 hours of coding and only 6 hours of searching... and I'm no better off than when I started. Here's my problem. I have a table (tmpShell) and it has 12 columns. It's a basic table with no constraints - used for temporary reporting. As we insert data, I have to extract an ID number (PatientId) and all column NAMES where the value for that PatientId is null.
Example:
PatientId Fname Lname DOB 123455 Sam NULL NULL
2345455 NULL Doe 1/1/1980
09172349 John Jone NULL
What I want to return is:
PatientId ErrorMsg
123455 Lname,DOB
2345455 Fname
09172349 DOB
Of course, if all columns have a value, the errormsg would be null.
I have tried and failed about 300 different pieces of code, but this appear to be the closest I can get. Unfortunately, this just returns EVERY column, not the nulls.
ALTER PROC [sp_aaShowAllNullColumns] @tableName VARCHAR(255) AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(4000); DECLARE @cols NVARCHAR(4000); DECLARE @tcols TABLE ( [colbit] NVARCHAR(255) ); --DECLARE @tablename VARCHAR(255) = 'tmpShell'; INSERT @tcols SELECT 'count(' + [columns].[name] + ') as ' + [columns].[name] + ', ' AS [colbit] FROM [sys].[columns] WHERE [columns].[object_id] = OBJECT_ID(@tableName); SELECT @cols = COALESCE(@cols, ', ', '') + [@tcols].[colbit] FROM @tcols; SELECT @cols = SUBSTRING(@cols, 1, ( LEN(@cols) - 1 )); SELECT @cols = ISNULL(@cols, ''); SELECT @sql = 'select patientid, count(*) as Rows' + @cols + ' from ' + @tableName + ' group by patientid having count(*) > 0'; CREATE TABLE [tmpShell2] ( [patientid] VARCHAR(15) ,[Rows] CHAR(2) ,[Rn] CHAR(2) ,[patId] CHAR(2) ,[fname] CHAR(2) ,[lname] CHAR(2) ,[dob] CHAR(2) ,[addr1] CHAR(2) ,[city] CHAR(2) ,[state] CHAR(2) ,[zip] CHAR(2) ,[country] CHAR(2) ,[psite] CHAR(2) ,[csite] CHAR(2) ,[ssite] CHAR(2) ,[scode] CHAR(2) ,[sfid] CHAR(2) ,[taskid] CHAR(2) ,[errormsg] CHAR(2) ); INSERT INTO [tmpShell2] EXEC [sys].[sp_executesql] @sql; DECLARE @tbl VARCHAR(255) = 'tmpShell2'; SELECT DISTINCT [TS].[patientid] , STUFF(( SELECT DISTINCT ', ' + [C].[name] FROM [tmpShell2] AS [TS2] JOIN [sys].[columns] AS [C] ON [C].[object_id] = OBJECT_ID(@tbl) WHERE [C].[name] NOT IN ( 'SFID', 'TaskId', 'ErrorMsg' ) AND [C].[name] IS NOT NULL FOR XML PATH('') ), 1, 1, '') FROM [tmpShell2] AS [TS]; DROP TABLE [dbo].[tmpShell2]; END; GO EXEC [sp_aaShowAllNullColumns] 'tmpShell';
</pre>
Post a Comment