2

sql view in how can I return the return value from the function into the function

select 

dbo.fn_CalculatePrice
(
p.Price, 
p.CargoPrice, 
p.Factor, 
p.PointRatio,
null, 
p.OperactivePromoCode, 
SELECT IncludingCargoPrice FROM [dbo].[fn_Parameter](),
SELECT IncludingProductKdv FROM [dbo].[fn_Parameter]()) AS Price 
From Product as p

[dbo].[fn_CalculatePrice] func parameters
(
    @Price money,
    @CargoPrice money,
    @CatFactor decimal(8,4), 
    @PointRatio decimal(8,4),
    @HBFactor decimal(8,4),
    @PromoCode nvarchar(50),
    @includingCargoPrice bit,
    @includingProductKdv bit
)

fn_Parameter func return table
{
IncludingCargoPrice bit,
IncludingProductKdv bit,
}

Error: Incorrect syntax near the keyword 'SELECT'

seleyemene
  • 23
  • 5
  • 1
    To begin with, having a `SELECT` statement as a parameter of a function is... (how to say is...?) not a recommended practice. Second, the `From Product as p` is obviously misplaced. Third, the selects within the parameters list do not warrant that they return a single value. Last, EDIT your question an make it readable,. – FDavidov Apr 27 '18 at 06:58
  • be aware that scalar valued functions perform RBAR (row by agonising row) – Mitch Wheat Apr 27 '18 at 08:20

1 Answers1

0

You can use any of these 2 approaches:

1) Determine each parameter as a scalar variable.

DECLARE @IncludingCargoPrice BIT = (SELECT IncludingCargoPrice FROM [dbo].fn_Parameter())
DECLARE @IncludingProductKdv BIT = (SELECT IncludingProductKdv FROM [dbo].fn_Parameter())

SELECT
    dbo.fn_CalculatePrice(
        p.Price, 
        p.CargoPrice, 
        p.Factor, 
        p.PointRatio,
        null, 
        p.OperactivePromoCode, 
        @IncludingCargoPrice,
        @IncludingProductKdv) AS Price
From 
    Product as p

2) SELECT them directly in your query (must make sure that the parameter table only contains one row, or the function will be applied multiple times for each product, by parameter row). You can filter the rows with WHERE or with a condition in an INNER JOIN (instead of a CROSS JOIN which I wrote as example).

SELECT
    dbo.fn_CalculatePrice(
        p.Price, 
        p.CargoPrice, 
        p.Factor, 
        p.PointRatio,
        null, 
        p.OperactivePromoCode, 
        X.IncludingCargoPrice,
        X.IncludingProductKdv) AS Price
From 
    Product as p
    CROSS JOIN [dbo].fn_Parameter() AS X
EzLo
  • 13,780
  • 10
  • 33
  • 38