Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I have a sql server table say "table_student" as

SQL
CREATE TABLE table_student
   (	StudentFirstName Varchar(200),
       StudentLastName  Varchar(200),
       StudentEmail     Varchar(50)
   )

and I have only one row in entire table for each corresponding column as,

SQL
Insert into table_student (StudentFirstName)
   Values('Ram')


Now its obvious that column StudentLastName and StudentEmail have null value in their corresponding rows.
I am writing following Store Procedure to get the list of column having null value as follow

SQL
create Procedure InsertStudentrecord

As
 Begin 
 
 
   declare @col varchar(255), @cmd varchar(max)

	DECLARE getinfo cursor for
	SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
	WHERE t.Name = 'table_student'

OPEN getinfo

FETCH NEXT FROM getinfo into @col
 print @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM table_student WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo
  
 End

and I get column name StudentLastName, StudentEmail and fetch status= 0 which is of course correct result. But when I replace the same table with temporary table say
"temp_student" with exactly same row
i.e
SQL
Insert into temp_student(StudentFirstName) Values('Ram')


I do not get any column with null row and fetch status= -1 when I looked for -1 I found that it mean "The FETCH statement failed or the row was beyond the result set."

I need to use temp table Please help me in that.
Posted
Updated 18-Nov-14 6:19am
v3
Comments
Kornfeld Eliyahu Peter 16-Nov-14 10:28am    
All your solution is far too complicated - why temp table? why cursor (this is a very expensive resource)?
rameshKumar1717 16-Nov-14 10:45am    
So how could I get the name of column in a given table having null row, I am new to SQL

try this?

SQL
create Procedure InsertStudentrecord
 
As
 Begin 
 
 
   declare @col varchar(255), @cmd varchar(max)
 
	DECLARE getinfo cursor FAST_FORWARD for
	SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
	WHERE t.Name = 'table_student'
 
OPEN getinfo
 
FETCH NEXT FROM getinfo into @col

 print @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM table_student WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)
 
    FETCH NEXT FROM getinfo into @col
END
 
CLOSE getinfo
DEALLOCATE getinfo
  
 End
 
Share this answer
 
v2
i think..
you want the columns with nulll value and you want to set the value to the corresponding field.
am i right???

so, just run a simple Sql Query to fetch the corresponding column which one you want to set with it's desired value and update the column.
thats it.
like this..

SQL
DECLARE c1 cursor for select Id,colum1,colum2 from table1 where column1 is null or colum2 is null
open c1

fetch next from c1 into @var1,@var2
WHILE @@FETCH_STATUS = 0
Begin
if @var1 is null
  begin
  // Update Query for column1
  end
if @var2 is null
  begin
  // Update Query for column2
  end

fetch next from c1 into @var1,@var2
End
CLOSE getinfo
DEALLOCATE 
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900