Dynamic SQL and Funtions
- by Unlimited071
Hi all, is there any way of accomplishing something like the following:
CREATE FUNCTION GetQtyFromID
(
@oricod varchar(15),
@ccocod varchar(15),
@ocmnum int,
@oinnum int,
@acmnum int,
@acttip char(2),
@unisim varchar(15)
)
AS
BEGIN
DECLARE @Result decimal(18,8)
DECLARE @SQLString nvarchar(max);
DECLARE @ParmDefinition nvarchar(max);
--I need to execute a query stored in a cell which returns the calculated qty.
--i.e of AcuQry: select @cant = sum(smt) from table where oricod = @oricod and ...
SELECT @SQLString = AcuQry
FROM OinActUni
WHERE (OriCod = @oricod) AND (ActTipCod = @acttip) AND (UniSim = @unisim) AND (AcuEst > 0)
SET @ParmDefinition = N'
@oricod varchar(15),
@ccocod varchar(15),
@ocmnum int,
@oinnum int,
@acmnum int,
@cant decimal(18,8) output';
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@oricod = @oricod,
@ccocod = @ccocod,
@ocmnum = @ocmnum,
@oinnum = @oinnum,
@acmnum = @acmnum,
@cant = @result OUTPUT;
RETURN @Result
END
The problem with this approach is that it is prohibited to execute sp_excutesql in a function...
What I need is to do something like:
select id, getQtyFromID(id) as qty
from table
The main idea is to execute a query stored in a table cell, this is because the qty of something depends on it's unit. the unit can be days or it can be metric tons, so there is no relation between the units, therefore the need of a specific query for each unit.
