SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Retrieve total null values from row.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mp2_admin
Starting Member

10 Posts

Posted - 11/13/2013 :  11:16:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/13/2013 :  12:37:40  Show Profile  Reply with Quote
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] '
Go to Top of Page

mp2_admin
Starting Member

10 Posts

Posted - 11/13/2013 :  13:30:37  Show Profile  Reply with Quote
Ahh that is where i was messing up. Where I was concatenating my variables. Thank You.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000