asked
Trying to capture vendor sales for a period
Trying to capture vendor sales using a, yes you heard it, CURSOR. Must use cursor, and must catch for name where Initial is null. Create Function VendorSales (@VendCode int) Returns @VendorSales Table ( VendorCode int , VendorName varchar(35) , Product varchar(35) , Customer varchar (35) , Units int , TotalValue money ) Begin Declare @Pcode varchar(15) Declare OnOff Cursor Fast_Forward FOR Select Distinct P_CODE From PRODUCT Where V_CODE = @VendCode Open OnOff Fetch Next From OnOff into @PCode While @@Fetch_Status = 0 Begin Insert Into @VendorSales Select @VendCode , V.V_NAME , P.P_DESCRIPT , IIF (C.CUS_INITIAL Is Not Null C.CUS_FNAME + ' ' + C.CUS_INITIAL + ' ' + C.CUS_LNAME ELSE C.CUS_FNAME + ' ' + C.CUS_LNAME) , Sum(L.LINE_UNITS) , Sum(L.LINE_PRICE*L.LINE_UNITS) From (CUSTOMER C Inner Join INVOICE I on C.CUS_CODE = I.CUS_CODE) Inner Join ((LINE L Inner Join PRODUCT P on L.P_CODE = P.P_CODE) Inner Join Vendor V on P.V_CODE = V.V_CODE)On I.INV_NUMBER = L.INV_NUMBER Group By V_NAME, P_DESCRIPT, (C.CUS_FNAME + ' ' + C.CUS_INITIAL + ' ' + C.CUS_LNAME) Fetch Next From OnOff Into @PCode End Close OnOff Deallocate OnOff RETURN End
Are all your questions homework/coursework? Not that we mind, it's just that we would rather know.
You dont say what your problem is, where are you having trouble that you would like to have some help?
First of all, don't use a cursor, but if you want to learn how to do it, don't do it in a function. Someone may be tempted to use it in a correlated subquery and causing a terrible performance issue. Second, make sure you have a where clause in the inner select, in your select you are getting all the records without any filter for the @VendCode. Why do you need the cursor for the vendors if you are getting all the vendors in every loop? Insert Into @VendorSales Select @VendCode , V.V_NAME , P.P_DESCRIPT , IIF (C.CUS_INITIAL Is Not Null C.CUS_FNAME + ' ' + C.CUS_INITIAL + ' ' + C.CUS_LNAME ELSE C.CUS_FNAME + ' ' + C.CUS_LNAME) , Sum(L.LINE_UNITS) , Sum(L.LINE_PRICE*L.LINE_UNITS) From (CUSTOMER C Inner Join INVOICE I on C.CUS_CODE = I.CUS_CODE) Inner Join ((LINE L Inner Join PRODUCT P on L.P_CODE = P.P_CODE) Inner Join Vendor V on P.V_CODE = V.V_CODE)On I.INV_NUMBER = L.INV_NUMBER WHERE v.V_CODE=@VendCode -- add this Group By V_NAME, P_DESCRIPT, (C.CUS_FNAME + ' ' + C.CUS_INITIAL + ' ' + C.CUS_LNAME) Third, there is no IIF statement in SQL server, it's only implemented in MS Access. Try not to take shortcuts when you try to learn something. It may turn out to be a dead end and you don't learn your lesson. My guess is that you have been using MS Access GUI to get your select statement. Fourth, make sure you study hard to learn when and why to use cursors. There are only a few situations you need a cursor.
