Dynamic SQL

From: Joe Maloney <jrpm_at_my-deja.com>
Date: 2000/08/11
Message-ID: <8n15ta$mog$1@nnrp1.deja.com>#1/1

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.

Received on Fri Aug 11 2000 - 00:00:00 CDT