May 20, 2010 at 4:36 am
hi Team,
Please see below:
set nocount on
go
begin try
begin transaction
declare @counter int
set @counter = 0
;with remove_table as
(
select row_number() over (order by table_name asc) as id, table_schema + '.' + table_name as t_name
from information_schema.tables
where 1=1
)
while @counter < (select count(id) + 1 from remove_table)
begin
set @counter = @counter + 1
print 'The counter is ' + cast(@counter as char)
end
commit transaction
end try
begin catch
if @@error <>0
rollback transaction
print ERROR_MESSAGE()
end catch
I receive a notification that Incorrect syntax near the keyword 'while'.
May 20, 2010 at 5:27 am
Halcyon (5/20/2010)
hi Team,Please see below:
set nocount on
go
begin try
begin transaction
declare @counter int
set @counter = 0
;with remove_table as
(
select row_number() over (order by table_name asc) as id, table_schema + '.' + table_name as t_name
from information_schema.tables
where 1=1
)
while @counter < (select count(id) + 1 from remove_table)
begin
set @counter = @counter + 1
print 'The counter is ' + cast(@counter as char)
end
commit transaction
end try
begin catch
if @@error <>0
rollback transaction
print ERROR_MESSAGE()
end catch
I receive a notification that Incorrect syntax near the keyword 'while'.
Cant follow a CTE with a While
From BOL
---------------
A CTE must be followed by a SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
---------------
May 20, 2010 at 5:37 am
Thank you, tommyh
Was just trying to get rid of tempory tables/table variables - is that possible?
May 20, 2010 at 5:48 am
If you need any kind off loop against the data then unfortunalty no. A CTE only exists for ONE statement. So cant do 2 selects against it for instance.
May 20, 2010 at 3:34 pm
Is there something I'm missing that won't allow you to do the following?
set nocount on
go
begin try
begin transaction
declare @counter int
set @counter = 0
while @counter < (select count(id) + 1 from
(
select row_number() over (order by table_name asc) as id, table_schema + '.' + table_name as t_name
from information_schema.tables
where 1=1
)as remove_table)
begin
set @counter = @counter + 1
print 'The counter is ' + cast(@counter as char)
end
commit transaction
end try
begin catch
if @@error <>0
rollback transaction
print ERROR_MESSAGE()
end catch
May 20, 2010 at 4:33 pm
very nice, thank you
one more? - i want to reference the data further in the script,
ie instead of print, use sp_executesql where id=1
would I still need to create a temp table or table variable?
May 20, 2010 at 4:43 pm
Giving it a try, it seems as if it will work just fine. Are you unable to try it out?
set nocount on
go
begin try
begin transaction
declare @counter int
declare @sql nvarchar(150)
set @counter = 0
while @counter < (select count(id) + 1 from
(
select row_number() over (order by table_name asc) as id, table_schema + '.' + table_name as t_name
from information_schema.tables
where 1=1
)as remove_table)
begin
set @counter = @counter + 1
set @sql = 'select ''The counter is ' + ltrim(rtrim(cast(@counter as char))) + ''''
exec sp_executesql @sql
end
commit transaction
end try
begin catch
if @@error <>0
rollback transaction
print ERROR_MESSAGE()
end catch
May 20, 2010 at 4:58 pm
it'll work fine, and if I would like to execute sql based on the t_name?
declare @t_table nvarchar(100)
set @t_table= (select t_name from (subquery) as remove_table where id=@counter?
May 20, 2010 at 5:01 pm
Looks like it should work.
May 20, 2010 at 5:08 pm
it just, looks clumsy:
set nocount on
go
begin try
begin transaction
declare @counter int
declare @sql nvarchar(150)
declare @t_name nvarchar(50)
set @counter = 0
while @counter < (select count(id) + 1 from
(
select row_number() over (order by table_name asc) as id, table_schema + '.' + table_name as t_name
from information_schema.tables
where 1=1
)as remove_table)
begin
set @counter = @counter + 1
set @t_name = (
select r.t_name
from
(
select row_number() over (order by table_name asc) as id, table_schema + '.' + table_name as t_name
from information_schema.tables
where 1=1) as r where
id=@counter)
set @sql = 'select ''The counter is ' + ltrim(rtrim(cast(@t_name as char))) + ''''
exec sp_executesql @sql
end
commit transaction
end try
begin catch
if @@error <>0
rollback transaction
print ERROR_MESSAGE()
end catch
I'll test performance tomorrow, thank you for your help
May 20, 2010 at 5:10 pm
Probably will be. I'd be interested to find out how the performance testing goes.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply