Returning Rowcount from a Function

Question

Post reply

Returning Rowcount from a Function

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
February 28, 2006 at 11:58 am

#93654

So I'd like to come up with a scalar UDF that returns the number of rows in a table or view passed to the UDF as a parameter.
I've done it in a Stored Procedure using Dynamic SQL and sp_ExecuteSQSL, but that's verboten in a UDF.
Anyone have a solution to this?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 1

nb: I looked at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=258288#bm258319

and that doesn't solve my problem as far as I can tell.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

Why do you need a UDF for this ?

To incorporate a table/view's record count in another query, just CROSS JOIN to the 'SELECT COUNT(*) ...' as a derived table.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 3

Hmm... Not sure how that would work. Here's the whole context:

I have a table of distribution lists. Each list has a name (descriptive) and a table/view name that is the actual distribution list. I would like to either have in the table a column that is the current rowcount of the table/view, or a view that lists the rows in the table with the current rowcount for each table/view in the table.

Distribution table:
Dist_Name varchar(100)
Dist_Table nvarchar(255)


Sample Distribution Table:
All Employees dbo.vw_all_employees
All Managers dbo.vw_all_managers
Top 300 Execs dbo.top300 (table)

How do I either add a formula based column to the Distribution table that gives me the current rowcount for the table/view, or create a view that lists the Distribution table entries plus the current rowcount for each table/view?

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

If you have the name of a SQL object represented as data in another SQL table, then you have no choice but to use dynamic SQL to use that object name in a query.

Your "Distribution" table is really meta-data.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

IMHO, the best way to pass a table to SP or UDF is to create table #<SP(UDF)_Name>, populate it with data and use it inside of SP or UDF.

But don't forget about the check

IF OBJECT_ID('tempdb..#<SP(UDF)_Name>') IS NULL

RETURN NULL

to avoid server errors.

But in your particular case I would not do it. I use to use COUNT(*) in subquery.

_____________
Code for TallyGenerator

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 6

Hi,

use this function which takes the table name

and returns the number of rows

CREATE FUNCTION Get_Rows(@tbl varchar(100)) returns int

as

Begin

Declare @i int

select @i=sum(rows)

from sysindexes where id=object_id(@tbl)

group by id

return @i

End

select dbo.Get_Rows('summary')

Out Put will be the Total No of Rows. In the specified period of time.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 7

Thanks for the contribution but

It's not accurate. I run it against a table with 5,000 rows and I get back 4,979. I need exact counts.
It doesn't work on Views.

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 8

NOW I have siligtly change my function,

i hope that it will provide to you better result in case of tables then it will think about the views.

Alter FUNCTION Get_Rows(@tbl varchar(100)) returns numeric(18,0)

as

Begin

Declare @i int

select @i=sum(rows)

from sysindexes where id=object_id(@tbl) and [name]=@tbl

group by id

return @i

End

Regards

Amit Gupta

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 9

Still returns 4,979. Select count(*) in QA returns 5,000.

Aren't row counts in the System tables notoriously inaccurate? I thought I'd seen that several times in other posts.

bar8393 SSC Enthusiast Points: 131 More actions · Answer 10

Yes those numbers will be inaccurate since they will only reflect what the index thinks is in the table.

If you rebuild index statistics just before you run the above query, you *should* get good numbers.

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 11

HI,

I HAVE SOLVED YOUR PROBLEM.

NOW IT WILL WORK IN BOTH CASES I.E. IN TABLES AND ALSE IN VIEWS.

for this you have to create a Table first,

Create Table tbl_v

(

i_val int

)

PASS TABLE NAME OR VIEW NAME TO A FUNCTION.

Create FUNCTION Get_Rows_ver(@val varchar(100)) returns varchar(100)

as

Begin

Declare @STR as varchar(100)

Declare @tbl table(vname varchar(50))

declare @i numeric(18,0)

set @STR='OSQL -S. -damit_db -Usa -P -Q"truncate table tbl_v" -oC:\AB.TXT'

EXEC master..xp_cmdshell @STR,NOOUTPUT

set @STR='OSQL -S. -damit_db -Usa -P -Q" insert into tbl_v select count(*) from ' + @val +'" -oC:\AB.TXT'

EXEC master..xp_cmdshell @STR,NOOUTPUT

select @i=ltrim(rtrim(i_val)) from tbl_v where isnumeric(ltrim(rtrim(i_val)))=1

return @i

End

I hope now it will work for you.

REGARDS

AMIT GUPTA

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Isn't it easier just to update statistics?

_____________
Code for TallyGenerator

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 13

No,

For this you have to depend on the system table and which is not a good practice

and alse it will not work in case of views.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 14

Unfortunately xp_cmdshell is restricted in our environment...