Plz correct my Stored Procedure

[happyIndian100]

Dear All,
i want to write a custom stored procedure to get the total records count by passing the table name to the stored procedure. Please help me to correct this stored procedure below...

CREATE procedure getrecordCount_SP
@TableName as varchar(30)
@TotalRecords as numeric output
as
begin
select @TableName = 'TableName'
declare @strSQL varchar(200)
select @strSQL = 'SELECT @TotalRecords = COUNT(*) FROM ' + @TableName
exec(@strSQL)
end

Thx in advance

 

[RobHVB6Sql]

/*********************************************************/
/* getrecordCount_SP                                     */
/*********************************************************/
if exists (select * from sysobjects where id = object_id('getrecordCount_SP') and sysstat & 0xf = 4)
   drop procedure getrecordCount_SP
GO

CREATE procedure getrecordCount_SP

 @TableName as varchar(50),
 @TotalRecords as numeric output

AS

 DECLARE @strSQL varchar(250)

BEGIN
 SELECT @strSQL = 'SELECT COUNT(*) FROM ' + @TableName
 EXEC(@strSQL)
END
GO

-- USAGE
--getrecordCount_SP 'tblPerson', 0

NOTES:
* No need to select @TotalRecords as it is an output parameter.
* Personally I like some uppercase, makes the code easier to read.

Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[happyIndian100]

Hi Rob Hasard,

Thankx for the reply...
but my requirement is i need to output TotalRecords as well as i need to return the result set.

Rgds
Happy


CREATE procedure getrecordCount_SP
@TableName as varchar(30)
@TotalRecords as numeric output
AS
BEGIN
select @TableName = 'TableName'
declare @strSQL varchar(200)
exec('SELECT * FROM ' + @TableName)

select @strSQL = 'SELECT @TotalRecords = COUNT(*) FROM ' + @TableName

exec(@strSQL)

END

 

[vidru]

You can eliminate the second SELECT/EXEC by using @@ROWCOUNT:

CREATE procedure getrecordCount_SP
  @TableName AS VARCHAR(30)
  @TotalRecords AS NUMERIC OUTPUT
AS
DECLARE @strSQL VARCHAR(200)
 
  SET @strSQL = 'SELECT * FROM ' + @TableName

  EXEC(@strSQL)

  SET @TotalRecords = @@ROWCOUNT

-dave