Dynamic Identifiers in a T-SQL Function

I want my code in a T-SQL function to dynamically reference tables based on database names passed in via a parameter.    For example, If i pass in 'MASTER' the function will look at the master database for the table,  if i pass in 'PUBS' it will look in the PUBS database for the table to select from etc.

code snippet attached.  In code snippet, the "TWO" is a hard coded reference to a database.  i want to be able to specify the database dynamically based on a parameter.

The accepted answer must be able to select from a database dynamically AND return value from the results set via the function
FUNCTION [dbo].[GetFiscalDate]
(
 @DocDate	DATETIME
)
RETURNS DATETIME
AS
BEGIN
 DECLARE @FiscalDate DATETIME
 
 SELECT @FiscalDate =
  DATEADD( 
   DD, 
   DATEDIFF (DD, TWO.DBO.SY40101.FSTFSCDY, 
    CONVERT(
     DATETIME, 
     CAST(TWO.DBO.SY40101.Year1 AS VARCHAR) + '/1/1'
     )
    ), 
   @DOCDATE)
 FROM TWO.DBO.SY40101
 WHERE TWO.DBO.SY40101.YEAR1 IN (
  SELECT TWO.DBO.SY40101.YEAR1
  FROM TWO.DBO.SY40101
  WHERE @DOCDATE BETWEEN TWO.DBO.SY40101.FSTFSCDY AND TWO.DBO.SY40101.LSTFSCDY
 )
 
 RETURN @FiscalDate
END

Open in new window

access_dudeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
inside a function, you CANNOT dynamically choose from a database from parameter, unless you want to code for each possible database a different call... like below
with a procedure, you could do it, using dynamic sql.

FUNCTION [dbo].[GetFiscalDate]
( @DocDate      DATETIME
, @db sysname
)
RETURNS DATETIME
AS
BEGIN
 DECLARE @FiscalDate DATETIME 
 if @db = 'ONE'
   select .... 
 if @db = 'TWO'
   select .... 
  ... etc .. 
 return @fiscaldate
END

Open in new window

0
Aneesh RetnakaranDatabase AdministratorCommented:
unfortunately sql server functions wont support dynamic sql without which you cant do this
0
access_dudeAuthor Commented:
What about in a stored procedure then using an OUT variable?  Prior conditions apply.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

chapmandewCommented:
It can be done in a stored procedure with OUTPUT params..just build the SQL string and pass it to sp_executeSQL.  Here is a link on how to use it.
http://technet.microsoft.com/en-us/library/ms188001.aspx
0
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE PROC GetFiscalDate(
@DocDate      DATETIME,
@db VARCHAR(30),
@DateOut datetime OUTPUT
)
AS
BEGIN
DECLARE @v_SQL nVARCHAR(4000)
DECLARE @Date Datetime


SET @V_SQL='SELECT @DateOut= someDateColumn FROM '+@db+'..TableName'

EXEC SP_EXECUTESQL @V_SQL,N'@DateOut datetime OUTPUT',@Date OUTPUT
SET @DateOut=@Date

RETURN @DateOut
END


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
0
maradamCommented:
Things are not that bad :). It is very supprising many people, but you CAN execute functions within function. What's more: you CAN execute them dynamicly. Here is how to do that.

1. Remove hardcoded database reference from GetFiscalDate function leaving only two part references.
2. Distribute the fuctnion among all the databases you need.
3. Copy-paste at the snippet :D. Function there calls your GetFiscalDate dynamicly. Below definition you have a trivial example how to call it. But remember: call the function only with dbs having GetFiscalDate function with one datetime parameter. If no function found or parameters don't match you will receive an error. You also can't check if database has such a function within your FN unless you add checking scalar function to literally EVERY database at your server and you use it in similar way.
create function dbo.getDbFiscalDate(@DocDate datetime, @db sysname)
returns datetime
as
begin
  declare @FullFN nvarchar(1000), @Res datetime
  set @FullFN = quotename(@db)+'.dbo.GetFiscalDate'
  exec @Res = @FullFN @Docdate
  return @Res
end
GO
select dbo.getDbFiscalDate('20080101',name) from sys.databases where name in ('ONE','TWO')

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.