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](http://stackoverflow.com/questions/727788/how-to-use-variable-for-database-name-in-t-sql) — Andriy M, Jan 16 '14 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, Jan 16 '14 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, Jan 16 '14 at 17:04
Thank you for your suggestion, I've added a new question [link](http://stackoverflow.com/questions/21164328/filtering-records-by-user-access-rights) — ako, Jan 16 '14 at 17:49

score 3 · Answer 1 · edited Jan 15 '14 at 17:02

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 '14 at 17:02

Trikaldarshiii

11,174
16
67
95

answered Jan 15 '14 at 17:00

DominionDave

39
2

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? – ako Jan 15 '14 at 17:06
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... – DominionDave Jan 15 '14 at 17:11
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 Jan 15 '14 at 17:22
It is not possible to use exec in function – ako Jan 15 '14 at 21:52

score 1 · Answer 2 · edited May 23 '17 at 11:49

1

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

edited May 23 '17 at 11:49

Community

1
1

answered Jan 15 '14 at 16:55

tfandango

113
6

Great, SQL Server will support dynamic SQL. – tfandango Jan 15 '14 at 17:01
See my comment to @DominionDave – ako Jan 15 '14 at 17:07

score 0 · Answer 3 · edited May 23 '17 at 12:21

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 '17 at 12:21

Community

1
1

answered Jan 16 '14 at 04:32

erikrunia

2,371
1
15
22

DB name as parameter in user-defined function

3 Answers3