Monday, February 20, 2012

passing table name as parameter and get count(*) value

Hi

I want to get a count(*) value (thro a procedure) for a table whose name will be passed as parameter

CREATE PROCEDURE PCount ( @.TName char(50), @.SCount int OUTPUT )
AS
Select @.SCount = Count(*) FROM @.TName

DECLARE @.TCnt int
EXEC PCount @.TName = 'tbl_users', @.TCnt = @.SCount OUTPUT

How to acheive this

Thanks-- the procedure if exists and then create it
if exists(select name from sysobjects where name = 'PCount' and type = 'P')
drop PROc PCount
GO
CREATE PROCEDURE PCount
(
@.TName varchar(50), @.SCount int OUTPUT
)
AS
SET NOCOUNT ON
EXEC ('select COUNT(*) as [count] into Tmp FROM '+@.TName+'')
select @.SCount = [count] from Tmp
drop table Tmp
SET NOCOUNT OFF

GO
-- SQL to test that SP
DECLARE @.TCnt int
EXEC PCount @.TName = 'sysobjects', @.SCount = @.TCnt OUTPUT
PRINT @.TCnt|||CREATE PROCEDURE PCount ( @.TName char(50))
AS
DECLARE @.NSQL NVARCHAR(1000)
DECLARE @.SCount INT
SET @.NSQL = ''
SET @.NSQL = @.NSQL + N'SELECT COUNT(*) FROM ' + @.TName
EXEC SP_EXECUTESQL @.NSQL, N'@.SCount INT OUTPUT', @.SCount OUTPUT

EXEC PCount @.TName = 'tbl_users'

No comments:

Post a Comment