Inserting a record count using Dynamic SQL

• Jay1971

  SSC Enthusiast

  Points: 114

  More actions

  May 24, 2004 at 8:40 am

  #107884

  Hi there,

  I have a stored procedure which creates a log of the traffic hitting a database and am having trouble getting part of my dynamic query to function.

  The code is listed below:

  EXECUTE sp_EXECUTESQL 'INSERT TableCounter (TableName) SELECT COUNT(*)FROM InterchangeDTA.dbo.' + @TableName

  This query is nested within a cursor and keeps on throwing up an error which states that I'm trying to insert a NULL value into the TableName column.

  I've tried de-bugging this in a number of ways without much success.

  If anyone can suggest how to fix this or alternative methods for placing the record count from a table inot a variable, it would be greatly appreciated.

  Cheers,

  Jay.

• Wayne Lawton

  Hall of Fame

  Points: 3532

  More actions

  May 24, 2004 at 9:00 am

  #507521

  Perhaps you could post the structure of the TableCounter table?

  Here is a possible solution:

  declare @SqlString varchar(500)

  set @SqlString = 'INSERT TableCounter (TableName) SELECT COUNT(*)FROM InterchangeDTA.dbo.' + @TableName

  execute (@SqlString)

   

  You may want to add a where clause to the insert so that the proper row in TableCounter is updated.  As coded, every row will be updated with the row count from the last table in your cursor.  You are ok if you are using TableCounter as a working table and it only contains one row. 

• David Burrows

  SSC Guru

  Points: 65143

  More actions

  May 25, 2004 at 5:37 am

  #507617

  alternative method for placing the record count from a table into a variable

  DECLARE @sql nvarchar(100),@Reccount int

  SET @sql = 'SELECT COUNT(*) FROM InterchangeDTA.dbo.' + @TableName

  EXECUTE sp_executesql @sql, N'@Reccount int output', @Reccount output

  Then you can insert

  INSERT INTO TableCounter (TableName) VALUES @Reccount

  btw, you state TableName as the column for insert but use the count as data! Is this right?

  Far away is close at hand in the images of elsewhere.
  Anon.

• Jonathan Stokes

  SSCrazy Eights

  Points: 9861

  More actions

  May 25, 2004 at 5:37 am

  #507616

  You will probababy find your code is generating the tablename without single quotes.

  Script below will work for you and is quicker to run for tables with large counts.

  /* declare variables */

  declare @tablename nvarchar(500)

  declare @sqlstring nvarchar(1000)

  /* set source table that count is be made from */

  set @tablename = 'srcTblName'

  /* Build string to execute */

  set @sqlstring = 'insert into dstTblname(dstColName)

  select max(rowcnt) from sysindexes where id in

  (select id from sysobjects where name = ''' + @tablename + ''')'

  /* execute string */

  execute (@sqlstring)

  However, you should build in some logic to check the source table exists and that return count should not be null if not intended.

  ---

  ------------------------------
  The Users are always right - when I'm not wrong!