Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006
    Posts
    12

    Unanswered: Read variable from exec statement

    Hi

    I have the following procedure. I would like to know how do I get the value count(*) from the exec statement?

    Thanks!

    ------------------------------------------------------------

    open cur_name
    fetch cur_name into @name

    while (@@sqlstatus != 2)
    BEGIN

    set stmt='select count(*) from '+@name
    exec(@stmt)

    Fetch cur_name into @name
    END

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    use a temp table
    Code:
    declare @tname varchar(30)
    set @tname='sysobjects'
    create table #t1 (name varchar(30), rowtotal decimal(11,0))
    exec ('insert into #t1 select '''+@tname+''',count(*) from '+@tname)
    select * from #t1 order by 1
    drop table #t1
    But how accurate do you need the count to be? Maybe this will be sufficient e.g.
    Code:
    select name = object_name(id)
    ,rowtotal = str(sum(rowcnt(doampg)),11,0)
    from sysindexes
    where id=object_id('sysobjects')
    --id in (select id from sysobjects where name like 'sys%')
    group by id
    order by 1
    Last edited by pdreyer; 05-03-07 at 09:00.

  3. #3
    Join Date
    Apr 2011
    Posts
    1

    a possible solution to your dynamic sql

    I wrote a post about this a while ago and there is a solution - you can use sp_executesql to perform all your magic. For the full post see here - Set @variable = exec(select…) & Dynamic SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •