Fred
asked on
Error Message creating UDF using JOINS
Trying to create a User Defined Function that accepts start and end date, and returns number of sales by card type VISTA between the dates a user inputs. I am using a JOIN of two tables
and common column is the CreditCardID
CREATE FUNCTION NumberOfSales
( @Startdate datetime, @EndDate datetime,@SalesCount INT )
RETURNS TABLE
AS
RETURN
(
SELECT
@SalesCount = CardType, COUNT (*) AS CountOfSales
FROM AdventureWorks2016.Sales.S alesOrderH eader a
INNER JOIN AdventureWorks2016.Sales.C reditCard b
ON a.CreditCardID = b.CreditCardID
WHERE CardType = 'Vista'
AND b.ModifiedDate BETWEEN '2007/10/01' AND '2013/10/31'
)
--Verify Function
SELECT * FROM NumberOfSales('2007/10/10' ,'2013/10/ 19')
Msg 102, Level 15, State 1, Procedure NumberOfSales, Line 9 [Batch Start Line 135]
Incorrect syntax near '='.
and common column is the CreditCardID
CREATE FUNCTION NumberOfSales
( @Startdate datetime, @EndDate datetime,@SalesCount INT )
RETURNS TABLE
AS
RETURN
(
SELECT
@SalesCount = CardType, COUNT (*) AS CountOfSales
FROM AdventureWorks2016.Sales.S
INNER JOIN AdventureWorks2016.Sales.C
ON a.CreditCardID = b.CreditCardID
WHERE CardType = 'Vista'
AND b.ModifiedDate BETWEEN '2007/10/01' AND '2013/10/31'
)
--Verify Function
SELECT * FROM NumberOfSales('2007/10/10'
Msg 102, Level 15, State 1, Procedure NumberOfSales, Line 9 [Batch Start Line 135]
Incorrect syntax near '='.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.