Hi, I have a sql server table say "table_student" as
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,
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
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
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.