logo

Creating SPs using Loops and Cursors

   

Added on  2019-09-16

2 Pages344 Words364 Views
 | 
 | 
 | 
Creating a SP using a while loop and cursor. 11/28/16 in class practice useMoviesgocreateprocsp_LabelFilm(@shortint,@mediumint)asbegin--declare local variablesdeclare@RunTimeint,@namechar(25),@IDint--define cursordeclarefilmcursorcursorFor (selectFilmID,FilmName,FilmRunTimeMinutesfromtblFilm)--open cursor Openfilmcursor--get first record from tableFetchnextfromfilmcursorinto@id,@name,@runtime--while there are more records, repeat While@@FETCH_STATUS= 0 Begin--check film length. label short, medium, long and print If@RunTime<=@shortprint@name+'Is a short Film'elseif@RunTime<=@mediumprint@name+'Is a medium film'elseprint@name+'Is a long film'--get next record Fetchnextfromfilmcursorinto@id,@name,@runtimeendClosefilmcursorDeallocateFilmcursorendExecuting the SP: useMoviesexecutesp_labelfilm120, 150 Question 2: useAllApplianceCogocreateprocsp_discount(@maxquantityint)asbegin--define local variablesdeclare@quantityint,@discountdec (4,2),@rowcountintset@quantity= 0 while@quantity<=@maxquantitybegin--what is @discount for @quantity ? If@quantity<= 50 set@discount= 0.0 elseif@quantity<= 150 set@discount= 0.05 elseset@discount= 0.10 --write @quantity & @discount into discount table select@rowcount=count(*)fromDiscountwhereQuantity=@quantityIf@rowcount= 0 insertintoDiscountvalues (@quantity,@discount)Else
Creating SPs using Loops and Cursors_1

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents