Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Thumbs down Unanswered: 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

  2. #2
    Join Date
    Sep 2003
    Location
    United States
    Posts
    65
    Provided Answers: 1

    Talking u can !

    -- 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
    >>
    >> Learn PHP/MySQL for free https://www.youtube.com/watch?v=mpQts3ezPVg
    >> Free Web Hosting with PHP, MySQL, Website Builder : http://www.000webhost.com/862861.html

  3. #3
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

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

    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'
    Shadow to Light

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •