April 15, 2005 at 10:38 am
I have a simple UDF that checks for division by zero errors.
If I create a table and use the UDF in a computed column - all is fine. It I try to do the EXACT same thing only in a #Temp table, it fails with <Invalid object name 'u_DivisionSafe'.>
I assume that becase #Temps are created in another DB, it cannot find the UDF ata all in my database.
However, even if I specify [MyDBName].[dbo].[MyUDFName] it still fails.
Any help ??
- B
April 15, 2005 at 1:29 pm
#temp tables are created in the tempdb and are only available during the current connection. Something like the following would not work:
begin
create #temp
commit
select * from #temp
From BOL:
Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server.
April 15, 2005 at 1:34 pm
it seems you cannot reference a udf in a computed col from any other database not just tempdb. You would have to create the function in tempdb.
i.e - this does not work either:
use pubs
go
create function udf_isblank(@n varchar(20) = null, @ret varchar(20))
returns varchar(20)
as
begin
declare @NULL int
set @NULL = case when isnull(@n,'') = '' then @ret
else @n
end
return @NULL
end
select pubs.dbo.udf_isblank('',0)
use northwind
go
--drop table test
create table test(
val int,
result as pubs.dbo.udf_isblank(val,0)
)
insert test (val) values(5)
insert test (val) values('')
insert test (val) values(null)
select * from test
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply