Assign row count of table where its name passed as a parameter to the stored procedure

Question

I am writing a stored procedure in SQL Server 2008 R2.

What I am trying to do is assigning row count of a table to a variable; however, I am only passing the table's name as a parameter to the stored procedure.

Here is the code that I wrote which is not working.

    CREATE PROCEDURE [dbo].[Foo]
        @inputTableName VARCHAR(50)
    AS
    BEGIN
       /*First Attempt*/
       DECLARE @numOfRows INT = (SELECT COUNT(*) FROM @inputTableName)
       /*Second Attempt*/
       DECLARE @numOfRows INT = (EXEC('SELECT COUNT(*) FROM ' + @inputTableName))
    END

In the first attempt, I am getting this error: Must declare the table variable '@inputTableName'

In the second one, I am getting this: Incorrect syntax near EXEC

Any help is appreciated.

score 0 · Answer 1 · answered Jul 05 '16 at 22:54

CREATE PROCEDURE [dbo].[Foo]
  @inputTableName SYSNAME
, @RowNumber      INT       OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
    Declare @Sql Nvarchar(max);

  SET @Sql = N' SELECT @RowNumber = COUNT(*) FROM  ' + QUOTENAME(@inputTableName)

  Exec sp_executesql @Sql
                    ,N'@RowNumber INT OUTPUT'
                    ,@RowNumber OUTPUT
END

Assign row count of table where its name passed as a parameter to the stored procedure

1 Answers1