3

I have a stored procedure that accepts a string parameter, a comma separated list of required column names that the SP should check a particular table and make sure those columns have data in them... If any record has missing data in those columns the sp should return the recordID and which columns having missing data.

I'm having trouble constructing the final query. I already split the comma separated list into a table variable. and am using a cursor to through each required column

DECLARE columnCursor CURSOR
    FOR SELECT * FROM @columnsTable
  OPEN columnCursor

  DECLARE @currentColumnName nvarchar(256);
  FETCH NEXT FROM columnCursor INTO @currentColumnName
  WHILE(@@FETCH_STATUS = 0)
  BEGIN

    ....

     FETCH NEXT FROM columnCursor INTO @currentColumnName
  END  
  • Will you know the table name to search at runtime? – World Wide DBA Sep 13 '12 at 20:46
5

Well you could do something like this which would avoid the cursor that you had in the query that you originally posted. Firstly, change the definition of your columns table so it looks like this:

DECLARE @columns TABLE
(
    ID INT IDENTITY NOT NULL,
    NAME NVARCHAR(128) NOT NULL
);

Then, using whatever method you have; split the string and populate the table above. After which you could do something like this to get your desired results:

EDIT: Changed the body of the loop based on your comment below.

CREATE TABLE ##results
(
    RecordID INT NOT NULL,
    ColumnName NVARCHAR(128) NOT NULL
);

DECLARE @i INT = 1;
DECLARE @j INT = (SELECT MAX(ID) FROM @columns);
DECLARE @c NVARCHAR(128); -- used to store column name
DECLARE @s NVARCHAR(max); -- to store the command....

WHILE(@i <= @j)
BEGIN
SET @c = (SELECT NAME FROM @columns WHERE ID = @i);

IF @i > 1
    SET @s += 'OR (' + @c + ' IS NULL) ';
ELSE
    SET @s += '(' + @c + ' IS NULL) ';

SET @s = 'INSERT ##results SELECT RecordID, ''' 
       + @c + ''' FROM some.table WHERE ' 
       + @c + ' IS NULL; '
@i += 1;
END
EXECUTE (@s);

SELECT * FROM ##results;
DROP TABLE ##results;

Be aware though that depending on the amount of rows you have in your table this could take a while...

I hope this helps you.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.