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'
Monday, February 20, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment