Database name as parameter in function

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 2005 Forums

Transact-SQL (2005)

Database name as parameter in function

Author

Topic

hlaold0
Starting Member

1 Post

Posted - 2012-08-24 : 05:41:30

Hello,
I have function and I have to have database name as parameters this function. So, I take parameter and I create SQL string in function for execute it.

select * from K2RDA.dbo.ReviewMandant('CZ')

But... Error

Msg 557, Level 16, State 2, Line 190
Only functions and extended stored procedures can be executed from within a function.

Do you have any idea?
Many thanks
Olda

Fragments of codes:
========================
CREATE FUNCTION dbo.ReviewMandant ( @Mandant varchar(2))
RETURNS
@ReviewMandantTab table (
[Mandant] [char](12) COLLATE Czech_CI_AS,
.
.
)
AS
BEGIN
DECLARE @DB varchar(10)
DECLARE @sql varchar(5000)
SELECT @DB = 'K2RDA_REDA_' + @Mandant

SET @sql = N'
INSERT INTO @ReviewMandantTab
SELECT ......
....
FROM ' + @DB + '..AKCE AKCE INNER JOIN K2RDA..CONF CON ON CON.Uzv=AKCE.Vlastnik'

EXEC sp_executesql @sql
RETURN

END
========================================

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-24 : 05:44:15

Think the error message sums it up.

Put this in-line and populate a temp table or table variable.
Or better - don't do it at all.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 10:31:37

why not make this into a stored procedure?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-08-24 : 12:55:29

If there is a limited number of databases that would be passed in, you could consider putting in an IF statement along the lines of:[CODE]if @db = 'ThisDB'
select ...
from ThisDB..AKCE AKCE INNER JOIN K2RDA..CONF CON ON CON.Uzv=AKCE.Vlastnik
else if @db = 'ThatDB'
select ...
from ThatDB..AKCE AKCE INNER JOIN K2RDA..CONF CON ON CON.Uzv=AKCE.Vlastnik
else
print 'Unrecognized Database Name'[/CODE]This gets ugly quickly if there are a large set of possible databases or if the code to be executed gets complex.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources