Dynamic sql in functions

[Naranek]

Hello Experts,

I am trying to make a function to get the rowcount of a table, where the table name is the argument into the function.

SELECT @SQLSENT = 'SELECT COUNT(*) FROM ' + @AS_TABLENAME + ';'

EXECUTE sp_executesql @SQLSENT, N'@LL_COUNT INTEGER OUTPUT', @LL_COUNT OUTPUT

RETURN @LL_COUNT

Trying to run this I get the following error message:
Server: Msg 557, Level 16, State 2, Procedure FMIS_GETTABLECOUNT, Line 12
Only functions and extended stored procedures can be executed from within a function.

Can anyone help me out with this one?

Thanks for any advice.

 

[zhavic]

if you want to select number of rows in some tables in one query, you can try this:

SELECT ( SELECT COUNT(*) FROM TABLE1 ) AS TABLE_COUNT1,
( SELECT COUNT(*) FROM TABLE2 ) AS TABLE_COUNT2,
( SELECT COUNT(*) FROM TABLE3 ) AS TABLE_COUNT3

I think, you can write your query without using a function, that returns a count of rows in some table.

Zhavic

 

[shadyness]

I do not think you should have to use a funtion at all. This should be explicit in SQL language as far finding a row count goes, and if you needed to make it dynamic, assign the answer to your rowcount query to a variable.

<== Some people say they are afraid of heights. With me its table widths. ==>

 

[Naranek]

The problem is that it is almost 200 tables, and they are stored in a TableInfo table. I must use a 3rd party tool to present the values, but due to limitations in that software I must make all the functions in the database itself. The software has no way of doing this. It is not a programming tool, but a mere presentation tool, with limited database handeling options.

The database tables will grow over time, and to avoid having to change the function, it cannot use the select table1, table2 approach. It is a bit long to explain, but the bottom line is that I have to use a function, it is the only way to make it work correctly.

So I was hoping that someone could tell what is wrong with statments above.


Best Regards,
Naranek

 

[zhavic]

In the body of user-defined functions you can't use &quot;nondeterministic functions and stored procedures&quot; and the sp_executesql is nondeterministic.

I show you example of stored procedure, that create a table which contains all table names from your &quot;TableInfo&quot; table and rowcounts for each of them:

For this example your &quot;TableInfo&quot; table has column named &quot;Table_name&quot;

*********************************************
CREATE PROCEDURE sp_fill_rowcounts

AS

DECLARE @cTable_name varchar(1000)
DECLARE @cSQL_Statement nvarchar(1000)
DECLARE @nCount integer

DECLARE table_names CURSOR
FAST_FORWARD
FOR SELECT Table_name
FROM TableInfo


OPEN table_names

FETCH NEXT FROM table_names
INTO @cTable_name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cSQL_Statement = 'SELECT @nCount = COUNT(*) FROM ' + @cTable_name
EXECUTE sp_executesql @cSQL_Statement, N'@nCount integer OUTPUT', @nCount OUTPUT

INSERT INTO #table_names_and_rowcounts VALUES ( @cTable_name, @nCount )

FETCH NEXT FROM table_names
INTO @cTable_name

END

CLOSE table_names
DEALLOCATE table_names

**************************************

This stored procedure fills existing temporary table with names and rowcounts of your tables, to use it, try this:

CREATE TABLE #table_names_and_rowcounts ( Name char(100), Row_count integer )

EXECUTE sp_fill_rowcounts

SELECT * FROM #table_names_and_rowcounts


Zhavic