Retrieve total null values from row.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Retrieve total null values from row.

Author

Topic

mp2_admin
Starting Member

10 Posts

Posted - 2013-11-13 : 11:16:37

Hi,
I am trying to get how many null fields i have in a row. But I am having trouble delcaring a variable (@tlbcnt) and using the variable in a subtraction routine below.. Can someone point out what I am doing incorreclt?

Thank You


DECLARE @sql NVARCHAR(MAX) = ''
DECLARE @tlbcnt NVARCHAR(MAX) = ''

SET @tlbcnt = (Select COUNT(*) FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id WHERE t.object_id = OBJECT_ID('r_recipes'));

SELECT @sql = @sql + N'  CASE WHEN '+QUOTENAME(c.name)+N' IS NULL THEN 1 ELSE 0 END +'
FROM sys.tables t
JOIN sys.columns c
    ON t.object_id = c.object_id
WHERE 
    c.is_nullable = 1
AND t.object_id = OBJECT_ID('r_recipes')
SET @sql = N'SELECT recipe_id, abs(('+@sql +N'+0)- +@tlbcnt) AS Cnt FROM [r_recipes] '
EXEC(@sql)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-13 : 12:37:40

I'm not clear what you are trying to do, but I think you need to concatenate the variable into your string. For example:

SET @sql = N'SELECT recipe_id, abs(('+@sql +N'+0)- ' + @tlbcnt + N') AS Cnt FROM [r_recipes] '

mp2_admin
Starting Member

10 Posts

Posted - 2013-11-13 : 13:30:37

Ahh that is where i was messing up. Where I was concatenating my variables. Thank You.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources