Post reply

How 2 get count(*) when table name in variable?

  • April 17, 2007 at 3:35 pm

    #123548

    I need to place the record count of a table in a numeric variable.  The problem is the the table name is in a variable also.

    declare @records int, @tablename varchar(50)

    set @records = (select count(*) from @tablename) doesn't work. 

    I've tried every combination of set, select, execute, & string commands I can think of and nothing has worked so far.

    Suggestions, please!

     

  • April 17, 2007 at 3:52 pm

    #701042

    Well, the piece that's causing you problems here is your table name.  You cannot use a variable in the FROM clause of a DML statement.  So your existing syntax would work fine if the table name were static.  If you must store your table name in a variable, you'll be forced to use dynamic SQL in the form of EXEC('sql select string here') or sp_executeSQL 'sql select string here'.  The problem you now have is that you cannot assign the results of either one of these methods into a variable.  I would suggest making a stored procedure where you will execute the dynamic sql and have the procedure return the row count into a variable in the form of:

    EXEC @Records = proc_Call_Your_StoredProcedure @TableName

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • April 17, 2007 at 3:52 pm

    #701041

    declare @records int, @tablename varchar(50), @SQL nvarchar(4000)

    SET @SQL = 'SELECT @rec = count(*) from dbo.[' + @tablename + ']'

    exec sp_executesql @SQL, N'@rec int OUTPUT', @rec = @records OUTPUT

    _____________
    Code for TallyGenerator

  • April 17, 2007 at 5:55 pm

    #701050

    Thanks Sergiy......that worked just great!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply