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 wanted to show row and column where field is null.

i already see this example here

but i don't understand xml code.

my code is like this

if exists (select * from Header where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null )    
    begin
        declare @error1 as varchar (50) 
    select @error1=[id] from Header  where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null  

        declare @error2 AS varchar(50) 

    DECLARE ocur CURSOR FOR 
    select [id] from Header where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null 

    OPEN ocur

    FETCH NEXT FROM ocur 
    INTO @error1

    WHILE @@FETCH_STATUS = 0

    BEGIN
        --set @error2 = @error2 + @error1 + ', '
        set @error2 = 'error -' + @error1 

        insert into yan values(@error2,@uploads, GETDATE() )


        FETCH NEXT FROM ocur INTO @error1
    END

        --insert into table_error values(@error2,@uploads, GETDATE() )
    CLOSE ocur
    DEALLOCATE ocur


    end
else
    begin   
            insert code
            end

so i just need row code that show where column is null.

Thanks

share|improve this question
    
You should learn to avoid cursors. Tbhey are huge SQL antipattern. Learn to use set-based techniques (which are usually more consise as well and easier to maintain). This should help you get started: wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them –  HLGEM Jan 16 '13 at 15:42

2 Answers 2

there is no need for using slow cursors ...

Insert into table_error
select 'error - ' + Cast(ID as Varchar(20)), @uploads, GetDate() 
from Header 
where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null
share|improve this answer
    
WHERE condition could be written shorter:-) [name]+[zip]+ cast([age] as varchar)+[city] is Null –  msi77 Jan 16 '13 at 18:56
    
@msi77 this would be AND not OR –  bummi Jan 16 '13 at 19:50
    
thanks for answering but i need column and row so the error massage can be specific –  Yan Susanto Jan 21 '13 at 12:08
    
@bummy, Concatenation will be NULL, if any column is NULL. This is OR, isn't it? –  msi77 Jan 22 '13 at 13:54
    
@msi77 sorry, of course, usually you are right Declare @a Varchar(10) Declare @b Varchar(10) Select @a ='Test' SET CONCAT_NULL_YIELDS_NULL OFF Select @a+@b SET CONCAT_NULL_YIELDS_NULL ON Select @a+@b –  bummi Jan 22 '13 at 15:26

Without XML (hardcoded)

declare @Header table (id int,name varchar(30),zip varchar(10),age int, city varchar(30))
insert into @Header Values(1,'Test','123',21,null);
insert into @Header Values(2,NULL,'123',21,null);
insert into @Header Values(3,NULL,NULL,21,null);
insert into @Header Values(4,'Test','123',21,null);
insert into @Header Values(5,'Test',NULL,21,null);
Select ID
, Case when Name is null then 1 else 0 end
+ Case when zip is null then 1 else 0 end
+ Case when age is null then 1 else 0 end
+ Case when City is null then 1 else 0 end 
as cnt

from @Header

with the output

id     cnt
1   1
2   2
3   3
4   1
5   2
share|improve this answer

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.