• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Dynamic SQL in User-defined function.

How can I execute and return Decimal result of my dynamic SQL query in a user defined function?
My function is like this:

CREATE FUNCTION Invt_GetItemQty(@GroupBy nvarchar(50), @ItemRef nvarchar(50),   @Property1 nvarchar(50), @Property2 nvarchar(50), @Property3 nvarchar(50),
@WarehouseNo int, @CalculateFromYear int, @RefDate DateTime, @AccountStartDate DateTime)  
RETURNS decimal

AS  
BEGIN

DECLARE       @strSQL      nvarchar(500),
            @strItemRef       nvarchar(50),
            @strProperty1       nvarchar(50),
            @strProperty2       nvarchar(50),
            @strProperty3       nvarchar(50),
            @QtyTotal      decimal(18,4)
Set @strItemRef=''
Set @strProperty1=''
Set @strProperty2=''
Set @strProperty3=''

----@GroupBy in format Ref_P1_P2_P3----
IF PATINDEX( '%Ref%', @GroupBy)>0
      SET @strItemRef=' AND ItemRef=N'''+@ItemRef+''''
IF PATINDEX( '%P1%', @GroupBy)>0
      SET @strProperty1=' AND Property1=N'''+@Property1+''''
IF PATINDEX( '%P2%', @GroupBy)>0
      SET @strProperty2=' AND Property2=N'''+@Property2+''''
IF PATINDEX( '%P3%', @GroupBy)>0
      SET @strProperty3=' AND Property3=N'''+@Property3+''''
            
Set @strSQL='SELECT SUM(dbo.Invt_GetItemQtyInRefDateTime1( ItemID, '+str(@WarehouseNo)+', '+str(@CalculateFromYear)+', '''+Convert(CHAR(10), @RefDate, 101)+''', '''+ Convert(CHAR(10), @AccountStartDate, 101)+''' ))
From Item
Where ItemID>-1 '+@strItemRef+@strProperty1+@strProperty2+@strProperty3

Return Exec(@strSQL)

END


I got error in the last statement Return Exec(@strSQL).  How can I return my decimal SUM() ?
Thanks.






0
kennon2000
Asked:
kennon2000
2 Solutions
 
podiluskaCommented:
You're returning a set of records currently.
Assign the result to a variable in your @strSQL,
ie:
 Set @strSQL='SELECT @ret = SUM...

execute the sql,
then return the value
0
 
illCommented:
you cannot use EXEC in function. see "create function" in BOL, where is the list of limitations.
workarounds:
1. if possible, rewrite exec to if...else or case statements
2. if not, use stored procedure instead the function
0
 
kennon2000Author Commented:
Set @strSQL='SELECT @ret = SUM...
..
I have tried before, doesn't work.

ill,
1. I don't want rewrite as you can see there are many combinations in the query
2. I would like to use my function in query like SELECT myFunction(ItemID), that cannot be replaced with stored procedure, right?

If Exec cannot be used, how to return the Sum?
0
 
illCommented:
if you're able( as SA) map your (local) as linked server. there is also a possibility to use procedure very similar way to
functions using openquery : http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21130029.html.
speed impact is none.
0
 
RichardCorrieCommented:
try
CREATE FUNCTION Invt_GetItemQty(@GroupBy nvarchar(50), @ItemRef nvarchar(50),   @Property1 nvarchar(50), @Property2 nvarchar(50), @Property3 nvarchar(50),
@WarehouseNo int, @CalculateFromYear int, @RefDate DateTime, @AccountStartDate DateTime)  
RETURNS decimal

AS  
BEGIN
Declare @return Decimal

SELECT @Return = SUM(dbo.Invt_GetItemQtyInRefDateTime1( ItemID, @WarehouseNo, @CalculateFromYear, @RefDate, @AccountStartDate)
From Item
Where ItemID>-1
AND
       (
                      (PATINDEX( '%Ref%', @GroupBy)>0
                     and
                        Property2= @Property2
                    )
       OR
                   PATINDEX( '%Ref%', @GroupBy) = 0
     )
AND
    (
        (
            PATINDEX( '%P1%', @GroupBy)>0
        and
            Property1=@Property1
        )
   or
        PATINDEX( '%P1%', @GroupBy)=0
    )
AND
    (
        (
            PATINDEX( '%P2%', @GroupBy)=0
        and
            Property2=@Property2
        )
   or
        PATINDEX( '%P2%', @GroupBy)=0
    )
AND
    (
        (
            PATINDEX( '%P3%', @GroupBy)=0
        and
            Property3=@Property3
        )
   or
        PATINDEX( '%P3%', @GroupBy)=0
    )

Return @return
end

/Richard
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now