Dynamic SQL Code Questions

Hi all--Given a table with the following structure on SQL Server 2005 SP2:


CREATE TABLE [dbo].[Contact_Data](

[COMPANY] [nvarchar](255) NULL,

[CONTACT] [nvarchar](255) NULL,

[LASTNAME] [nvarchar](255) NULL,

[DEPARTMENT] [nvarchar](255) NULL,

[TITLE] [nvarchar](255) NULL,

[PHONE1] [nvarchar](255) NULL,

[PHONE2] [nvarchar](255) NULL,

[PHONE3] [nvarchar](255) NULL,

[FAX] [nvarchar](255) NULL,

[EXT1] [nvarchar](255) NULL,

[EXT2] [nvarchar](255) NULL,

[ADDRESS1] [nvarchar](255) NULL,

[ADDRESS2] [nvarchar](255) NULL,

[CITY] [nvarchar](255) NULL,

[STATE] [nvarchar](255) NULL,

[ZIP] [float] NULL,

[KEY1] [nvarchar](255) NULL,

[KEY2] [float] NULL,

[KEY3] [nvarchar](255) NULL,

[KEY4] [nvarchar](255) NULL,

[U_COMPANY] [nvarchar](255) NULL,

[U_CONTACT] [nvarchar](255) NULL,

[U_LASTNAME] [nvarchar](255) NULL,

[U_CITY] [nvarchar](255) NULL,

[U_STATE] [nvarchar](255) NULL,

[U_KEY1] [nvarchar](255) NULL,

[U_KEY2] [nvarchar](255) NULL,

[U_KEY3] [nvarchar](255) NULL,

[U_KEY4] [nvarchar](255) NULL,

[U_KEY5] [nvarchar](255) NULL,

[Corporate] [nvarchar](255) NULL,

[Account_Role] [nvarchar](255) NULL,

[Premise_Role] [nvarchar](255) NULL

)



I'm trying to write some dynamic SQL that cycles through all the column names in a table, checks through all the rows in that table for any blank rows of data and assign the rows count result to a variable called 'blankrow'. Here's my code so far:


declare @blankrow int,

@sqlstr nvarchar(2048),

@sqlstr2 nvarchar(2048)



set @sqlstr2 = N'select name from sys.all_columns

where object_id = 1831117714'

set @sqlstr = N'SELECT count(*)

FROM [Contact_Data]

where (' + @sqlstr2 + ') IS NOT null'

exec sp_executesql @sqlstr

set @blankrow=convert(nvarchar, @sqlstr)

print @blankrow

set @sqlstr='The number of blank rows found= ' + CAST(@@ROWCOUNT as Varchar(5)) + ' rows.'

go



Here are the errors I get:

1. For this code:


set @sqlstr2 = N'select name from sys.all_columns

where object_id = 1831117714'

set @sqlstr = N'SELECT count(*)

FROM [Contact_Data]

where (' + @sqlstr2 + ') IS NOT null'

exec sp_executesql @sqlstr


Error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

2. For this code:


set @blankrow=convert(nvarchar, @sqlstr)

print @blankrow

set @sqlstr='The number of blank rows found= ' + CAST(@@ROWCOUNT as Varchar(5)) + ' rows.'

I get this error:
Conversion failed when converting the nvarchar value 'SELECT count(*)
FROM [Contact' to data type int.


For the latter error, I know I have to change type from int to nvarchar, but don't know how to do this yet in this context. I suspect I have to do something like this:

execute sp_executsql @sqlstr, @blankrow int, @blankrow=CAST(@sqlstr as nVarchar(5))

Ideas?

Thanks,
Jon