?
Solved

passing database name as param into UDF

Posted on 2007-10-03
4
Medium Priority
?
281 Views
Last Modified: 2010-03-19
Hi,

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,
Cheers,
Steve.

---------------------------------

CREATE FUNCTION sfn_SHDIT_get_Call_Status_Totals
(
      @Database varchar(20),
      @Status_Type varchar(12),
      @Operator varchar(255)
)
RETURNS int
AS
BEGIN

      

      DECLARE @Total int

      EXEC('


      SET @Total =

            (
                        

                  SELECT      count(*)

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

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

                  
            )
      ')

      RETURN @Total


END
GO
0
Comment
Question by:ive5005s
  • 2
4 Comments
 
LVL 143

Expert Comment

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

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

Accepted Solution

by:
Lowfatspread earned 200 total points
ID: 20005949
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)

e.g.
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                  
Union
SELECT      call_operatorid,'DBNAME2' as DBNAme,call_status,count(*)  as Calls
   FROM      DBNAME2.dbo.CALL AS a
  Group by call_operatorid,Call_Status                  
Union
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
0
 

Author Comment

by:ive5005s
ID: 20005995
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.

Cheers.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20006860
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...
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

850 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