Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    OH
    Posts
    10

    Question Unanswered: A function to return number of records

    Hello everyone,
    I am trying to write a function that returns the number of records present in a table. But in this case i want to pass 'TABLENAME' as a parameter to this function and then retrieve numrecords henceforth.
    This is how I'm currently trying to retrieve it -->

    FUNCTION GetNumRecords(@tablename as nvarchar(20))
    return int as
    begin
    Declare @numrecs as int
    Set @numrecs = (Select Count(*) from @tablename)
    return @numrecs
    end

    I'm getting the following error:

    ----
    Must declare the variable '@tablename'.
    ---

    But i am already passing @tablename as a parameter to the function and I dont exactly understand where the problem is!! Why can't I use a local variable in a SELECT statement like this.

    Any input is highly appreciated,
    Thank you.

  2. #2
    Join Date
    Apr 2003
    Posts
    30
    use execute

  3. #3
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    Or use:

    SELECT @numrecs = rowcnt FROM sysindexes
    WHERE id = object_id(@TableName)
    AND indid in (0,1)


    Each table must have at least 1 entry in sysindexes.
    If the table has the clustered index, its indid will be 1.
    If the table has no clustered, it will have a record with indid = 0.
    --
    kukuk

  4. #4
    Join Date
    May 2003
    Location
    Amsterdam, the Netherlands
    Posts
    4

    Declaration syntax

    I'm afraid you're confusing SQL Server and Oracle syntax

    Never use: DECLARE variable_name AS datatype
    but instead: DECLARE variable_name datatype

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Refer to this link to get the result.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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