passing database name as param into UDF

Posted on 2007-10-03
Last Modified: 2010-03-19

I'm trying to create a UDF scalar function (using SQL 2005) where I can pass in a parameter which will contain the name of the database I want to execute a simple Tsql statement on.

This is my statement (below)... I've tried using "Exec" but that isn't allowed in a UDF.

Please help,


CREATE FUNCTION sfn_SHDIT_get_Call_Status_Totals
      @Database varchar(20),
      @Status_Type varchar(12),
      @Operator varchar(255)


      DECLARE @Total int


      SET @Total =


                  SELECT      count(*)

                  FROM      '+@Database+'.dbo.CALL AS a

                  WHERE      CALL_STATUS = '+@Status_Type+'
                              CALL_OPERATORID like '+@Operator+'


      RETURN @Total

Question by:ive5005s
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    short answer:
    you cannot.

    long answer:
    this is not possible, due to the limitations of sql server for the functions.
    LVL 50

    Accepted Solution

    i agree in principle with angeliii

    you could however consider creating a view in the database(s) to allow access to the data you require...
    (you could even consider making it a partitioned view)

    Create View as DBCalls
    SELECT      call_operatorid,'DBNAME1' as DBNAme,call_status,count(*)  as Calls
       FROM      DBNAME1.dbo.CALL AS a
      Group by call_operatorid,Call_Status                  
    SELECT      call_operatorid,'DBNAME2' as DBNAme,call_status,count(*)  as Calls
       FROM      DBNAME2.dbo.CALL AS a
      Group by call_operatorid,Call_Status                  
    SELECT      call_operatorid,'DBNAME3' as DBNAme,call_status,count(*)  as Calls
       FROM      DBNAME3.dbo.CALL AS a
      Group by call_operatorid,Call_Status                  

    Select Calls
       from DBCalls
     Where Call_operatorid=X
       and Call_status=x
       and DBname=x

    Author Comment

    Thanks to you both for responding.

    Lowfatspread, I began to code something along those lines, and I think it's my best solution to this problem. So I'll accept that as the solution.

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    I see, that is indeed possible as workaround.

    I suggest though to use UNION ALL instead of UNION, for performance reasons, if you go the "view" way.
    better would be to use IF() in the function...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now