March 16, 2010 at 3:32 pm
Hi there,
I was wondering if someone could tell me. I am learning dynamic sql and finding the '''s hard going and have decided to proceed with splitting them up. The following program message says its fine but I get no results window. Could someone tell me why?
cheers Craig
***************code*********************
Use AdventureWorksDW2008
go
declare @Tablename varchar(400)
declare @x varchar(200)
declare curtable cursor
for
select name
from sysobjects
where type='U'
order by name
open curtable
fetch next
from curtable
into @x
while @@FETCH_STATUS=0
begin
set @x=@x+'select count(*)'+','+' '+@Tablename+' '+'from AdventureWorksDW2008.dbo.'+@Tablename
exec (@x)
fetch next
from curtable
into @Tablename
end
close curtable
deallocate curtable
March 16, 2010 at 3:40 pm
The easiest thing to do here is to display your query (using select or print) to verify it is what you expect before executing it as a query.
March 16, 2010 at 3:43 pm
You need to assign to @Tablename instead of @x in your first fetch statement.
Also, just use "set @x ='select..."
instead of "set @x =@x+'select..."
March 16, 2010 at 3:51 pm
Thanks, but have changed it to @Tablename in first fetch statement but tried "print" and getting nothing, it seems its correct though, anything else I've missed?
March 16, 2010 at 3:52 pm
cheers thanks heaps for this, used print and it does nothing. Is the set @x statement actually correct?
March 16, 2010 at 4:07 pm
This works should show you how to do it
Bryan
declare @Tablename varchar(400)
declare @x varchar(200)
declare curtable cursor
for
select name
from sysobjects
where type='U'
order by name
open curtable
fetch next
from curtable
into @x
set @x='select count(*) from '+@Tablename
print @x
exec (@x)
while @@FETCH_STATUS=0
begin
set @x='select count(*) from '+@Tablename
print @x
exec (@x)
fetch next
from curtable
into @Tablename
end
close curtable
deallocate curtable
March 16, 2010 at 4:09 pm
The following code worked as bad as a [c u r s o r] is supposed to but at least it gave the expected result (had to change it to AdventureWorksDW though, since I'm using SS2K5). Side note: I consider the non-set-based solution as a "valid option" for this task, but definitely not in general (I can't even type the evil word...). 🙂
FETCH NEXT
FROM curtable
INTO @Tablename
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @x
SET @x='select count(*)'+','+' '+@Tablename+' '+'from AdventureWorksDW2008.dbo.'+@Tablename
PRINT (@x)
FETCH NEXT
FROM curtable
INTO @Tablename
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply