Dynamic SQL and Funtions

Posted by Unlimited071 on Stack Overflow See other posts from Stack Overflow or by Unlimited071
Published on 2010-06-08T16:27:25Z Indexed on 2010/06/08 16:42 UTC
Read the original article Hit count: 161

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.

© Stack Overflow or respective owner

Related posts about tsql

Related posts about stored-procedures