Simple query on select statement using variables...

• Warren Peace

  SSCertifiable

  Points: 5871

  More actions

  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

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  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)
  Intro to Tally Tables and Functions