Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

dynamic sql example Expand / Collapse
Author
Message
Posted Tuesday, March 16, 2010 3:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 12, 2010 3:12 PM
Points: 27, Visits: 36
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
Post #884265
Posted Tuesday, March 16, 2010 3:40 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
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.

Converting oxygen into carbon dioxide, since 1955.

Post #884269
Posted Tuesday, March 16, 2010 3:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 6,842, Visits: 13,373
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..."





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #884270
Posted Tuesday, March 16, 2010 3:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 12, 2010 3:12 PM
Points: 27, Visits: 36
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?
Post #884274
Posted Tuesday, March 16, 2010 3:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 12, 2010 3:12 PM
Points: 27, Visits: 36
cheers thanks heaps for this, used print and it does nothing. Is the set @x statement actually correct?
Post #884275
Posted Tuesday, March 16, 2010 4:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 19, 2012 9:49 PM
Points: 38, Visits: 51
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



Kindest Regards,

bryan.oliver@quest.com

quest.com
Post #884290
Posted Tuesday, March 16, 2010 4:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 6,842, Visits: 13,373
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





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #884292
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse