Link to home
Start Free TrialLog in
Avatar of Fred
FredFlag for United States of America

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.SalesOrderHeader a
                                          INNER JOIN                  AdventureWorks2016.Sales.CreditCard      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 '='.
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial