Dynamic SQL question - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Dynamic SQL question

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-25 : 18:25:39

I have a temp table #Temp_Table has these 2 columns among others.
Table_Name, Column_name
Lists.......ListID
Lists.......ItemName
Lists.......ItemAbbreviation
Lists.......ItemDescription

From the table named in the Table_Name column I want to populate a new column named "Populated" with the count of rows based on this select:
SELECT COUNT(*) FROM #t.Table_Name WHERE Len(#t.Column_name)<> 0

My attempt at writing this select is as follows.

DECLARE @Sql varchar(7000)

select * from #Temp_Table #t
while #t.Ordinal_position IS NOT NULL

set @Sql =
'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + #t.Column_name + 'WHERE Len(' + #t.Column_name + ')<> 0)
where Ordinal_position IS NOT NULL'

EXEC(@Sql)

I know this syntax in incorrect. How should this dynamic SQL be written?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-25 : 19:33:21

Tried this?

set @Sql =
'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + #temp_table.Column_name + ' WHERE Len(' + #temp_table.Column_name + ' )<> 0)
where Ordinal_position IS NOT NULL'

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-25 : 19:55:25

DECLARE @Sql varchar(7000)

select * from #Temp_Table #t
while #t.Ordinal_position IS NOT NULL

set @Sql =
'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + #temp_table.Column_name + ' WHERE Len(' + #temp_table.Column_name + ' )<> 0)
where Ordinal_position IS NOT NULL'

EXEC(@Sql)

I gave this a try and got htese errors

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "#t.Ordinal_position" could not be bound
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "#temp_table.Column_name" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "#temp_table.Column_name" could not be bound.

Is this something that will have to be done with a cursor?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-25 : 20:20:33

Ok, syntax should look like following. But column populated will have same value in all affected rows:

select * from #Temp_Table
while Ordinal_position IS NOT NULL

Update #temp_table
set populated = (SELECT COUNT(*) FROM #temp_table t1 WHERE Len(t1.Column_name)<> 0)
where Ordinal_position IS NOT NULL

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-25 : 20:47:36

You are correct, the column populated will have same value in all affected rows using your example syntax.

My objective is to end up with a result that looks like this:

Table_Name Column_name Populated
Lists.......ListID............22
Lists.......ItemName..........10
Lists.......ItemAbbreviation..17
Lists.......ItemDescription...5

That is why I am using dynamic SQL. The values in the LEN(<column name>) and From <table name> must change based on the row being processed in #Temp_Table. That is why i need to know how to fix this my dynamic SQL so those values will dynamicly change.

DECLARE @Sql varchar(7000)

select * from #Temp_Table #t
while #t.Ordinal_position IS NOT NULL

set @Sql =
'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + #t.Column_name + 'WHERE Len(' + #t.Column_name + ')<> 0)
where Ordinal_position IS NOT NULL'

EXEC(@Sql)

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-25 : 21:11:10

You can't select rows from column, and don't think first select statement has anything to do in this case. By the way, what does the 'while' do in that select statement?

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-25 : 21:16:38

The purpose of the WHILE is to provide a means by which each row in #Temp_Table would be evaluated. So that the values in the LEN(<column name>) and From <table name> would change based on the row being evaluated. Is there qnother way to get a count for each column listed in Column_name?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-25 : 21:22:44

But wrong syntax.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-25 : 23:56:16

what is the correct syntax?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-27 : 01:55:27

You can find it in books online.