November 20, 2007 at 5:49 am
Hi Experts,
I want to check 'NULL' values against a table columns which would be added dynamically.
For example,
Scenario 1 :
----------------
Table Name : Emp
Structure (Assumption):
Eno Ename
--------------------
Scenario 2 :
----------------
Table Name : Emp
Structure (Assumption):
Eno Ename Salary
--------------------
Scenario 3 :
----------------
Table Name : Emp
Structure (Assumption):
Eno Salary
--------------------
Scenario 4 :
----------------
Table Name : Emp
Structure (Assumption):
Eno Ename Salary DOB
--------------------
Scenario 5 :
----------------
Table Name : Emp
Structure (Assumption):
Eno Salary DOB
--------------------
as mentioned above columns of the table would increase or decrease. But i want to check all of those columns has contain 'NULL' value or not.
for example if i write like
select isnull(Eno,''),isnull(Ename,'') from emp
it will check only the above two columns. I want to check dynamically.
Really i dont know whether dynamic sql in the possible solution for this problem or not.
so i am expecting dame good answers from dame good experts.
karthik
November 22, 2007 at 1:58 am
Need ideas !
karthik
November 22, 2007 at 11:17 pm
any idea ?
karthik
November 26, 2007 at 2:25 am
Any expert answer for my question ?
karthik
November 27, 2007 at 6:23 am
It is very urgent. I am in the edge to close this request. Experts input are truly welcome.
karthik
November 28, 2007 at 11:40 am
Far from being an expert, but good at culling the best or what I believe are the best features from the experts input.
How about this for a start - I am sure you could improve it.
DECLARE @TableName AS VARCHAR(64)
DECLARE @C_Name AS VARCHAR(64)
DECLARE @Sql AS VARCHAR(4000)
DECLARE @Irows AS INT
SET @Irows = 0
SET @Sql = 'SELECT ISNULL('
SET @TableName = 'name of your table'
/*Must be STATIC or @@CURSOR_ROWS will not function as required */
DECLARE C_Name CURSOR LOCAL STATIC
FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @TableName
OPEN C_Name
FETCH NEXT FROM C_Name INTO @C_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Irows = @Irows + 1
IF @Irows = @@CURSOR_ROWS
BEGIN
SET @Sql = @Sql + @C_Name + ')'
END
ELSE
BEGIN
SET @Sql = @Sql + @C_Name + '), ISNULL('
END
FETCH NEXT FROM C_Name
END
SET @Sql = @Sql + ' FROM ' + @TableName
SELECT @Sql
--PRINT '*' + @Sql + '*'
CLOSE C_Name
DEALLOCATE C_Name
GO
November 28, 2007 at 12:09 pm
Hey bittucket your code doen't cycle to the next column. But based on that here is a simpler method commonly used
DECLARE @TableName AS VARCHAR(64)
DECLARE @Sql AS VARCHAR(4000)
SET @TableName = 'LocDetail'
SELECT @SQL = IsNull(@SQL + ', ','') + 'ISNULL([' + COLUMN_NAME + '],'''')' FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @TableName
SET @SQL = 'SELECT ' + @SQL + ' FROM ' + @TableName
EXECUTE(@SQL)
However you will have trouble with the above if any column is non-alphanumeric. What would you want to happen say with an INT column and are you just trying to find columns which are null and not a particular record?
November 29, 2007 at 7:55 am
Thank you Antares686 I stand corrected. Fishing for some goodness from my post ... I am guessing that it got you to post yours and yours does work so that helps KarthiKeyan and myself. Heck here is the hidden value of these forums ... we are (I am) always learning
Again Antares686 - Thank you
December 3, 2007 at 11:19 pm
Thank you Antares686.I got the solution.
karthik
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply