I would like to know if the sp_executesql sproc can be used in a function. I created the following function and it compiled:

IF EXISTS (SELECT name from sysobjects
       WHERE name = 'f_RecordCount'
       AND type = 'FN')
   DROP FUNCTION f_RecordCount
GO


CREATE FUNCTION dbo.f_RecordCount
(@TblName Nvarchar(256))
Returns BigInt
AS

Begin
declare @RECCNT int
declare @CMD Nvarchar(100)
--declare @TblName varchar(256)


SET @CMD = 'SELECT @RECORDCNT=count(*) from ' + @TblName

exec sp_executesql @CMD,
                   N'@RECORDCNT int out',
                   @RECCNT out

RETURN cast(@RECCNT as BigInt)
END

When I try to use the function I get the following error:

Server: Msg 557, Level 16, State 2, Procedure f_RecordCount, Line 16
Only functions and extended stored procedures can be executed from within a function.


Any ideas if I can use sp_executesql in a function?

Thanks!!

The error says it all. You can only call extended stored procedures.
Jim

Thanks, Jim for your reply. Can you help me to understand (or tell me where to find info on) extended stored procedures. Can you suggest how to create a function that will accept a table name as its parameter and return the rowcount? For some reason the index rowcounts don't always match the table rowcounts.

Thanks!!

CT

You can find help on extended stored procedures in Books On Line that came with sql server. But I don't think that will help you. Use a stored procedure instead of a function and your code should work fine.

Hi Jim,

This is procedure that I came up with (code stolen from various places) to count the records of the tables. I get the record count from sysindexes and actually count records for each table. I found the counts out of sync for a few tables in different databases. I spoke with the DBA, and he has included an update statistics process to the databases which I am told should keep the counts the same. Using a cursor in a database with more than 1000 tables is very slow.

DECLARE tnames_cursor CURSOR
FOR
   SELECT SO.id, SO.name, IST.TABLE_CATALOG, MAX(NDX.rows) NDXrowCount
   FROM sysobjects SO
   INNER JOIN INFORMATION_SCHEMA.TABLES IST
   ON SO.name = IST.TABLE_NAME
   INNER JOIN sysindexes NDX
   ON SO.id = NDX.id
   WHERE type = 'U'
   GROUP BY SO.id, SO.name, IST.TABLE_CATALOG
   ORDER BY SO.name
OPEN tnames_cursor
DECLARE @tablename sysname
DECLARE @id bigint
DECLARE @database varchar(100)
DECLARE @NDXrowCnt bigint
/*
DECLARE @TempTable TABLE
    (
    [primeKey] [int] IDENTITY (1, 1) NOT NULL ,
    [id] [int] NOT NULL ,
    [tableName] [sysname] NULL ,
    [database] varchar(200) NULL ,
    [TBLrecordCount] [numeric](15, 0) NULL ,
    [NDXrecordCount] [numeric](15, 0) NULL ,
    [rCountDate] [datetime] NULL
    )
*/
FETCH NEXT FROM tnames_cursor INTO @id, @tablename, @database, @NDXrowCnt
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN
    declare @Table varchar(100)
    declare @RECCNT int
    declare @CMD Nvarchar(100)
    set @Table = @tablename --'dss.dbo.tabledata1'
    SET @CMD = 'SELECT @RECORDCNT=count(*) from [' + @Table + ']'
    exec sp_executesql @CMD,
                   N'@RECORDCNT int out',
                   @RECCNT out--,@TableName,
                   --@TblName
    insert into DSS.dbo.TableData (id, tablename, [database], TBLrecordcount, NDXrecordcount)
    values (@id, @tablename, @database, @NDXrowCnt, @RECCNT)
   END
   FETCH NEXT FROM tnames_cursor INTO @id, @tablename, @database, @NDXrowCnt
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

Thanks again for your input!

Sincerely,

Charles Thompson, Jr.

Glad to help.
Cursors will be slow. I suggest using a while loop to loop instead of a cursor. Loops will be slow as well, but overall should be faster than a cursor.