Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SQL
Sorry folks. Wrong Forum.
In article <8n15ta$mog$1_at_nnrp1.deja.com>,
Joe Maloney <jrpm_at_my-deja.com> wrote:
> I am trying to write a dynamic sql routine that returns a value.
>
> Specifically, I want to build a sql command string such as
> select count(*) into :@indexcount from database.dbo.sysindexes
> where the database name is a variable (set by a cursor loop through
> sysdatabases.)
>
> THe script is something like
>
> Cursor getdbname is select name from master.dbo.sysdatabases
>
> open getdbname
> fetchnext getdbname into database
> while @@fetch
> select @commandline='select count(*)
> from '+database+'.dbo.sysindexes'
> exec(@commandline) @indexcount real OUTPUT
> select @printline=convert(varchar,@indexcount)
> print @printline
> end
>
> I have tried every variation I can thing of, from calling other stored
> procedures, using 'into' as part of the string, etc. but I have not
> been able to figure out a way to get the 'count(*)' into @indexcount
in
> a dynamic string statement.
>
> Is there a way to do this in TSQL or has my mental density finally
> exceeded reality?
>
> --
> Joseph R.P. Maloney, CCP,CSP,CDP
> MPiR, Inc.
> 502-451-7404
> some witty phrase goes here, I think.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
-- Joseph R.P. Maloney, CCP,CSP,CDP MPiR, Inc. 502-451-7404 some witty phrase goes here, I think. Sent via Deja.com http://www.deja.com/ Before you buy.Received on Fri Aug 11 2000 - 00:00:00 CDT