Link to home
Start Free TrialLog in
Avatar of ive5005s
ive5005s

asked on

passing database name as param into UDF

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

short answer:
you cannot.

long answer:
this is not possible, due to the limitations of sql server for the functions.
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ive5005s
ive5005s

ASKER

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.
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...