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.






kennon2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.