Creating a SP using a while loop and cursor.

Added on - 16 Sep 2019

  • Dissertation

    type

  • 2

    pages

  • 344

    words

  • 114

    views

  • 0

    downloads

Showing pages 1 to 1 of 2 pages
Creating a SP using a while loop and cursor. 11/28/16 in class practiceuseMoviesgocreateprocsp_LabelFilm(@shortint,@mediumint)asbegin--declare local variablesdeclare@RunTimeint,@namechar(25),@IDint--define cursordeclarefilmcursorcursorFor(selectFilmID,FilmName,FilmRunTimeMinutesfromtblFilm)--open cursorOpenfilmcursor--get first record from tableFetchnextfromfilmcursorinto@id,@name,@runtime--while there are more records, repeatWhile@@FETCH_STATUS=0Begin--check film length. label short, medium, long and printIf@RunTime<=@shortprint@name+'Is a short Film'elseif@RunTime<=@mediumprint@name+'Is a medium film'elseprint@name+'Is a long film'--get next recordFetchnextfromfilmcursorinto@id,@name,@runtimeendClosefilmcursorDeallocateFilmcursorendExecuting the SP:useMoviesexecutesp_labelfilm120,150Question 2:useAllApplianceCogocreateprocsp_discount(@maxquantityint)asbegin--define local variablesdeclare@quantityint,@discountdec(4,2),@rowcountintset@quantity=0while@quantity<=@maxquantitybegin--what is @discount for @quantity ?If@quantity<=50set@discount=0.0elseif@quantity<=150set@discount=0.05elseset@discount=0.10--write @quantity & @discount into discount tableselect@rowcount=count(*)fromDiscountwhereQuantity=@quantityIf@rowcount=0insertintoDiscountvalues(@quantity,@discount)Else
desklib-logo
You’re reading a preview
card-image

To View Complete Document

Become a Desklib Library Member.
Subscribe to our plans

Unlock This Document