function call - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

function call

Author

Topic

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-15 : 03:23:44

hi, i have a function which selects from system tables.

my problem is if i call the function within the database on which it was created, it shows the data i need,
but when i call it from another database, it still shows the data from the database where it was created.

is there a workaround?

thanks in advance...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 05:15:56

well you could always put the function in the other server...

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-15 : 05:36:26

lolz, this is the same server but different databases...
i can do that, but i need to place all "utility" objects in one database, some sort of master maintenance database...

thanks for suggesting it though...

coz if the master database objects can be accessed via other databases, then i guess there's a way?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-09-15 : 05:43:50

I suppose you could prefix with the DBname, but then you may need to do this in dynamic sql...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 05:50:26

yes that's true

well you could do it:


CREATE FUNCTION dbo.YourFunc
(
@db int
)  
RETURNS @MyTable table 
AS  
BEGIN 

if @db=1
begin
select * from Northwind..sysobjects
end
else if @db=2
begin
select * from master..sysobjects
end
return
end

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources