Putting Insert Statement into User Defined Function

[M8tt]

I have written the insert statement below to load some db info into a table.

insert into [DB_SIZES](
[NAME], [FILE_PATH], [SIZE]
)
select name, filename, (size * 8)
from sysfiles

I have created this as a Stored Procedure but I think this is going to limit me to using it within a single database where as what I want to do is to be able to run it in any database, hence I have been trying to create it as a UDF.

I've tried several different things (the below is an example) but can't get it to work.

Could anyone offer me some advice on a) if I'm right that the UDF will run across databases and b) where I'm going wrong with the code?

Many Thanks in advance, Matt

create function fn_update_db_sizes
returns @DB_SIZES table
([NAME] nvarchar (40),
[FILE_PATH] nvarchar (255),
[SIZE] int
)
as
begin
insert into @DB_SIZES
select name, filename, (size * 8)
from sysfiles
return
end

 

[FlashMerlot]

Quick answer ... write not allowed in UDFs

Quote from BOL ...

"You may have included a statement in the BEGIN-END block that has side effects, which is not allowed in user-defined functions. Function side effects are any permanent changes to the state of a resource that has a scope outside the function. Changes can be made only to local objects such as local cursors or variables. Examples of actions that cannot be performed in a function include modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user."