Post reply

Simple query on select statement using variables...

  • November 29, 2007 at 12:58 pm

    #69213

    The following works...

    DECLARE @TblName sysname

    Declare @MyQuery varchar(255)

    DECLARE @RecordCount Int

    SELECT @RecordCount = (Select Count(*) From Table1)

    PRINT @RecordCount

    My problem is that this doesn't work...

    DECLARE @TblName sysname

    Declare @MyQuery varchar(255)

    DECLARE @RecordCount Int

    Set @TblName = 'Table1'

    Set @MyQuery = 'Select Count(*) From ' + @TblName

    SELECT @RecordCount = EXECUTE(@MyQuery)

    What I want to do is build my query with variables, execute my query and put the results into another variable. I'm pretty sure it's simple but I'm blanking out.

    The error I get is...

    Msg 156, Level 15, State 1, Line 6

    Incorrect syntax near the keyword 'EXECUTE'.

    You're probably thinking why doesn't he just use this query...

    SELECT o.name TableName ,i.rows TblRowCount

    FROM sysobjects o

    INNER JOIN sysindexes i ON (o.id = i.id)

    WHERE o.xtype = 'u'

    AND i.indid < 2

    Because I want to make the above one work. It's just one of those things where you know it CAN work and you can't stop till you get it to work eventhough you know another way.

    Thanks

  • November 29, 2007 at 9:17 pm

    #755790

    Heh... I know the feeling...

    Study sp_ExecuteSQL in BOL... it'll show you how to do exactly what you want.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

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