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.
| Author |
Topic |
|
arifpratama
Starting Member
1 Post |
 Posted - 2015-03-19 : 08:20:34
|
DECLARE
@name nvarchar(max),
@sql nvarchar(max),
@column nvarchar(max)
DECLARE cur_files CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
select t.name,c.name from sys.tables t
inner join sys.columns c on t.object_id = c.object_id
where c.name = 'date'
open cur_files;
FETCH NEXT FROM cur_files INTO @name, @column
WHILE @@FETCH_STATUS = 0
begin
set @sql = 'select top 1 cast(date as date)Date,count(1)Count from '+@name+'
group by cast(date as date)
order by cast(date as date) desc
'
execute(@sql);
FETCH NEXT FROM cur_files
INTO @name, @column
END
close cur_files
DEALLOCATE cur_files
please help me
how to select table name on this query??
i want to select table name from the cursor
select top 1 table_name cast(date as date)Date,count(1)Count from '+@name+'
group by cast(date as date)
order by cast(date as date) desc
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-03-19 : 09:01:46
|
This perhaps?
set @sql = 'select top 1 '''+@name+''' AS MyTableName, cast(date as date)Date,count(1)Count from '+@name+'
group by cast(date as date)
order by cast(date as date) desc
'
But you need to guard against the Table Name (or any other parameter you include like this) containing a single-quote !!!! otherwise you are SERIOUSLY at risk from SQL Injection.
A Table Name containing a space would also prevent your query working (surround the table name with "[" and "]" - but then you need to safeguard to make sure it doesn't contain those characters ...) Look into using QUOTENAME for those safeguards |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-19 : 09:04:31
|
use this in your dynamic sql:
set @sql = 'select top 1 ''' + @name+ ''' as Table_Name, cast(date as date)Date,count(1)Count from '+@name+'
group by cast(date as date)
order by cast(date as date) desc
'
|
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2015-03-19 : 12:52:38
|
and make sure to read this too www.sommarskog.se/dynamic_sql.html
Madhivanan
Failing to plan is Planning to fail |
|
|
|
|
|
|
select table name