Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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  
share|improve this question
    
Will you know the table name to search at runtime? –  Mr.Brownstone Sep 13 '12 at 20:46
    
yes, I will. Thanks for the help –  Vazgen Sep 13 '12 at 20:48
1  
why did you remove the rest of the query? –  Mr.Brownstone Sep 13 '12 at 21:13
    
rolled back because this edit was detrimental to the post. –  jcolebrand Sep 14 '12 at 20:15
add comment

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
thank you so much, it there any way this can be made to return also the names of the missing columns that are missing data in that record. I'm very new to sql sorry if its so obvious but I just dont see it :( –  Vazgen Sep 13 '12 at 23:20
1  
I have altered the query to do this for you now. –  Mr.Brownstone Sep 14 '12 at 7:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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