Can SQL Select Stmt Contain a Variable for Table Name? 1

[JohnBates]

hi VB people,

I need to cycle thru dozens of tables and get the record count for each table.

It is easy to do if I hard-code the table name in the Select statment.

But I really want to just assign the table name to a string variable, and let the Select stmt use the variable as the table name.

Thanks, John

Dim strSQL As String
Dim strTable As String
strTable = "Regions"

Set GMcn = New ADODB.Connection
Set GMrs = New ADODB.Recordset

GMcn.ConnectionString = "Provider=sqloledb;Data Source=GM;Initial Catalog=gm;Integrated Security=SSPI;"

strSQL = "Select Count(*) As Recs From &strTable&"

GMcn.Open

'** get syntax error when opening the recordset **
'** (This stmt is a bit unconventional, but it does work
'** when the table name is hard-coded in strSQL)
Call GMrs.Open(strSQL, GMcn, adOpenForwardOnly, adLockReadOnly, adCmdText)

 

[johnwm]

strSQL = "Select Count(*) As Recs From " & strTable & ";"


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[CasperTFG]

What's the purpose? Speed?

If it's speed yoru after make the statement into a stored procedure?

Like...

CREATE PROCEDURE sp_T_CountRecords
  @oTbl nvarchar(256)
AS
  SELECT COUNT(*) FROM @oFld
GO

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[mmilan]

Casper - you been down the boozer?

You declare the input parameter as @oTbl, then start using @oFld instead...

Replace the @oFld with @oTbl and this should work.

mmilan

 

[noun]

Server: Msg 137, Level 15, State 2, Procedure sp_T_CountRecords, Line 4
Must declare the variable '@oTbl'.


Are you sure you can define a variable for the "from" clause? That produces an error!

 

[noun]

But this way works

CREATE PROCEDURE sp_T_CountRecords
  @oTbl nvarchar(256)
AS
  declare @tsql varchar(100)
  set @tsql='SELECT COUNT(*) FROM ' + @oTbl
  exec ( @tsql )
GO

Using "exec", you can execute dynamically created statements.
By the way, i read somewhere, that isn't convenient to name store procedures with the "sp_" prefix.