Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Passing variable table name

Answered Passing variable table name

  • Friday, January 25, 2013 10:49 PM
     
     
    Sign In to Vote
    0

    Hello,

    I'm trying to pass variable table name and find the count of each table. I'm using following statement, please guide..

    SET @sql1='SELECT COUNT(1) FROM '+@name
    EXEC sp_executesql @sql1, @count OUTPUT
    INSERT INTO #temp (Table_Name,Record_Count)
    VALUES (@name,@count)

    Thanks

     

All Replies

  • Friday, January 25, 2013 10:59 PM
     
     Answered Has Code
    Sign In to Vote
    1
     
    DECLARE @Count INT;
SET @sql1 ='SELECT @Count=COUNT(1) FROM'+@name 
EXEC sp_executesql 
	@query=@sql1,
	@params=N'@Count INT OUTPUT',
	@Count=@Count OUTPUT
--PRINT @Count
--Use the @Count from there
INSERT INTO #temp (Table_Name,Record_Count)
VALUES (@name,@count)


    Narsimha


    • Edited by Naarasimha Friday, January 25, 2013 11:00 PM
    • Marked As Answer by dvsrk Friday, January 25, 2013 11:30 PM
    •  
     
  • Friday, January 25, 2013 11:08 PM
    Moderator
     
     
    Sign In to Vote
    1

    Blog on counting rows different ways:

    http://www.sqlusa.com/bestpractices2005/alltablesrowcount/

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

     
  • Saturday, January 26, 2013 11:33 AM
     
     
    Sign In to Vote
    1

    Two small modifications:

    SET @sql1 ='SELECT @Count=COUNT(1) FROM'+@name

    Should be

      SET @sql1 = 'SELECT @Count=COUNT(1) FROM ' + quotename(@name)

    That is, there must be a space after FROM, and use quotename, in case the table name includes funny characters.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se