DB name as parameter in user-defined function

Question

Is it possible to use DB name as parameter in user-defined function?

For example I've tried to use concatenation but it doesn't work in this way:

create function fn_getEntityOwners (@dbName varchar, @entityId bigint)
returns table
as
return 
select
    OWNERS.USER_ID 
from 
    @dbName + '.dbo.OWNERS' as OWNERS
where 
    OWNERS.ENTITY_ID = @entityId

UPDATE: I use sql server

possible duplicate of How to use variable for database name in t-sql — Andriy M
– Andriy M, Commented Jan 16, 2014 at 16:37
@AndriyM My question is more specific because I need to use DB name as parameter in user-defined function and function imposes some restrictions. — ako
– ako, Commented Jan 16, 2014 at 16:51
Well, you just can't parametrise a name (DB name, column name, table name). Almost all the answers (both there and here) suggest using dynamic SQL. You can't use dynamic SQL in a function, which means you are getting nowhere with this particular question. I would suggest you ask a new question, where you might want to elaborate a little on what you are trying to do, and where you would ask specifically for alternatives to using dynamic SQL (or, perhaps, for alternatives to using a function?). — Andriy M
– Andriy M, Commented Jan 16, 2014 at 17:04
Thank you for your suggestion, I've added a new question link — ako
– ako, Commented Jan 16, 2014 at 17:49

Mohit · Accepted Answer · 2014-01-15 17:02:54Z

3

Put your code in function into a string and execute by dynamic sql

DECLARE @SQL varchar(2000)

SELECT @SQL = 

'select
    OWNERS.USER_ID 
from 
    ' + @dbName + '.dbo.OWNERS as OWNERS
where 
    OWNERS.ENTITY_ID = ' + @entityId


EXEC(@SQL)

edited Jan 15, 2014 at 17:02

Mohit

11.3k16 gold badges70 silver badges95 bronze badges

answered Jan 15, 2014 at 17:00

DominionDave

392 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ako Over a year ago

Don't like this approach much because query becomes a string and isn't validated by SQL Server Management Studio. But anyway thank you for your solution. Other ideas?

DominionDave Over a year ago

are you worried about the database not existing and throwing an error? if so can always add code to check if exists before querying for the user_id...IF db_id(@dbName) IS NOT NULL BEGIN...

ako Over a year ago

No, I'm worried about situation when you make mistake in sql query but because it is just a string you will know this only after function execution. I've shown just a simple example but imagine that query inside function is big and complicated.

ako Over a year ago

It is not possible to use exec in function

Community · Accepted Answer · 2017-05-23 11:49:42Z

1

You need to use dynamic SQL to do this. What DB is this? Here's another SO question that relates

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Jan 15, 2014 at 16:55

tfandango

1538 bronze badges

2 Comments

tfandango Over a year ago

Great, SQL Server will support dynamic SQL.

ako Over a year ago

See my comment to @DominionDave

Community · Accepted Answer · 2017-05-23 12:21:28Z

0

You can also use sqlcmd mode for this (enable this on the "Query" menu in Management Studio).

:setvar dbname "TEST.dbo.OWNERS" 

select
    OWNERS.USER_ID 
from 
    $(dbname) as OWNERS
where 
    OWNERS.ENTITY_ID = @entityId

... this solution brought to you by Stack Overflow ...

whatup @DominionDave - Nice to have you on SO playa :-D

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Jan 16, 2014 at 4:32

erikrunia

2,4731 gold badge18 silver badges28 bronze badges

Collectives™ on Stack Overflow

DB name as parameter in user-defined function

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related