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
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_
(
@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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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...
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...
you cannot.
long answer:
this is not possible, due to the limitations of sql server for the functions.