{"id":2408,"date":"2021-01-02T18:26:31","date_gmt":"2021-01-02T18:26:31","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=2408"},"modified":"2021-01-02T18:26:32","modified_gmt":"2021-01-02T18:26:32","slug":"estructura-repetitiva-while-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2408","title":{"rendered":"Estructura repetitiva While SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile__D.png\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile__D.png\" alt=\"\" class=\"wp-image-2409\"\/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Usando While<\/strong><\/h2>\n\n\n\n<p>La estructura While es una estructura repetitiva que permite ejecutar un bloque de instrucciones mientras que una expresi\u00f3n l\u00f3gica sea verdadera.<\/p>\n\n\n\n<!--more-->\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n\n<p style=\"font-size:24px\"><strong>Sintaxis<\/strong><\/p>\n\n\n\n<p>While (Expresi\u00f3n L\u00f3gica)<br>Begin<br>Conjunto de instrucciones del bloque<br>[BREAK | CONTINUE]<br>End<br><strong>Donde<\/strong><br><strong>Expresi\u00f3n L\u00f3gica:<\/strong> es una expresi\u00f3n que retorna verdadero o falso. Los par\u00e9ntesis ser\u00e1n necesarios si la expresi\u00f3n l\u00f3gica tiene una instrucci\u00f3n Select.<br><strong>BREAK<\/strong><br>Provoca una salida del bucle WHILE m\u00e1s interno. Se ejecutan todas las declaraciones que aparecen despu\u00e9s de la palabra clave END, que marcan el final del ciclo.<br><strong>CONTINUE<\/strong><br>Hace que el ciclo WHILE se reinicie, ignorando cualquier declaraci\u00f3n  despu\u00e9s de la palabra clave CONTINUE.<\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicios<\/strong><\/p>\n\n\n\n<p><strong>Usando la base de datos Northwind<\/strong><br>use Northwind<br>go<\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>While dentro de un cursor (<a href=\"https:\/\/manualsqlserver.com\/?p=343\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Cursores<\/a>)<\/strong><br><strong>Cursor que reporte la lista de productos, si las unidades en orden son mayores al stock, mostrar COMPRAR URGENTE, de lo contrario mostrar STOCK ADECUADO<\/strong><\/p>\n\n\n\n<p>Declare cursorProductoComprasUrgente cursor for<br>SELECT<br>P.ProductID, P.ProductName,<br>P.UnitsInStock, P.UnitsOnOrder<br>from Products As P<br>Open cursorProductoComprasUrgente<br>Declare @Codigo int, @Descripcion nvarchar(40),<br>@Stock Numeric(9,2), @PorAtender Numeric(9,2)<br>Fetch cursorProductoComprasUrgente into @Codigo,<br>@Descripcion, @Stock, @PorAtender<br>Print &#8216;================ LISTADO ======================&#8217;<br>While (@@FETCH_STATUS = 0)<br>Begin<br>Declare @Mensaje nvarchar(20)<br>If (@PorAtender > @Stock)<br>Begin<br>Set @Mensaje = &#8216;COMPRAR URGENTE&#8217;<br>End<br>Else<br>Begin<br>Set @Mensaje = &#8216;STOCK ADECUADO&#8217;<br>End<br>Print &#8216;C\u00f3digo: &#8216; + STR(@Codigo)<br>Print &#8216;Descripci\u00f3n: &#8216; + @Descripcion + &#8216; Stock: &#8216; + Ltrim(STR(@Stock)) + &#8216; Por Atender: &#8216; + Ltrim(Str(@PorAtender))<br>Print &#8216;Mensaje: &#8216; + @Mensaje<br>Print \u00bb<br>Print &#8216;==============================================================&#8217;<br>Fetch cursorProductoComprasUrgente into @Codigo,<br>@Descripcion, @Stock, @PorAtender<br>End<br>Close cursorProductoComprasUrgente<br>Deallocate cursorProductoComprasUrgente<br>go<br><strong>El resultado se muestra en la siguiente imagen<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_01.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_01.png\" alt=\"\" class=\"wp-image-2410\" width=\"746\" height=\"692\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_01.png 862w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_01-300x278.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_01-768x713.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_01-795x738.png 795w\" sizes=\"auto, (max-width: 746px) 100vw, 746px\" \/><\/a><\/figure>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>En este ejercicio se va a insertar en una tabla intervalos de cada media hora, desde las 00 horas hasta las 24 horas y en un campo se va a especificar las horas, de 1 a 24.<\/strong><\/p>\n\n\n\n<p>drop table if exists Horas<br>go<br>create table Horas<br>(<br>Intervalo Time, Hora nchar(10)<br>)<br>go<br>&#8212; Insertar<br>Set nocount on<br>Declare @Vez int, @Hora Time, @Intervalo int<br>set @Vez = 1<br>Set @Hora = &#8216;0:00 am&#8217;<br>Set @Intervalo = 1<br>while (@Vez &lt;= 48)<br>Begin<br>if ((@Vez%2) = 0 )<br>Begin<br>insert into horas values (@Hora, \u00bb)<br>End<br>Else<br>Begin<br>insert into horas values (@Hora, @Intervalo)<br>Set @Intervalo = @Intervalo +1<br>End<br>Set @Vez = @Vez +1<br>Set @Hora = DATEADD(MI,30, @Hora)<br>End<br>go<br>set dateformat dmy<br>go<br>select format(H.Intervalo,&#8217;hh\\:mm&#8217; ) As Intervalo, Hora from horas As H<br>go<br><strong>El resultado se muestra en la siguiente imagen<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_02.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_02.png\" alt=\"\" class=\"wp-image-2411\" width=\"306\" height=\"886\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_02.png 203w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_02-104x300.png 104w\" sizes=\"auto, (max-width: 306px) 100vw, 306px\" \/><\/a><\/figure>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p><strong>Uso de la estructura While para recorrer los caracteres de una cadena de texto. Se va a crear una funci\u00f3n definida por el usuario que recibe como par\u00e1metro la cadena de texto.<\/strong><br>Create or alter function dbo.fduRetornaVocalesConsonantes (@Palabra nvarchar(200))<br>Returns nvarchar(100)<br>As<br>Begin<br>Declare @Dato nvarchar(200), @Letra nvarchar(1)<br>set @Dato = Upper(Replace(LTRIM(RTRIM(@Palabra)),&#8217; &#8216;,\u00bb))<br>Declare @Inicio int, @TotalVocales int, @TotalConsonantes int<br>set @Inicio = 1<br>Set @TotalVocales = 0<br>set @TotalConsonantes = 0<br>while @Inicio &lt;= LEN(@Dato)<br>Begin<br>set @Letra = SUBSTRING(@Dato,@Inicio,1)<br>if @Letra in (&#8216;A&#8217;,&#8217;E&#8217;,&#8217;I&#8217;,&#8217;O&#8217;,&#8217;U&#8217;,&#8217;\u00c1&#8217;,&#8217;\u00c9&#8217;,&#8217;\u00cd&#8217;,&#8217;\u00d3&#8217;,&#8217;\u00da&#8217;)<br>Begin<br>Set @TotalVocales = @TotalVocales +1<br>End<br>Else<br>Begin<br>Set @TotalConsonantes = @TotalConsonantes +1<br>End<br>Set @Inicio = @Inicio +1<br>End<br>Declare @Resultado nvarchar(200)<br>Set @Resultado = &#8216;La frase \u00ab&#8216; + Upper(@Palabra) + &#8216;\u00bb tiene &#8216; +<br>LTRIM(STR(@TotalVocales)) +&#8217; vocales y &#8216; + LTRIM(Str(@TotalConsonantes)) + &#8216; consonantes&#8217;<br>return @Resultado<br>End<br>go<\/p>\n\n\n\n<p><strong>Ejecutando la FDU para una cadena de texto<br><\/strong>Select dbo.fduRetornaVocalesConsonantes(&#8216;SQL Server Gu\u00eda Profesional&#8217;)<br>As &#8216;Resultado&#8217;<br>go<br><strong>El resultado se muestra en la siguiente imagen<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_03.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_03.png\" alt=\"\" class=\"wp-image-2412\" width=\"788\" height=\"173\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_03.png 693w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_EstructuraWhile_03-300x66.png 300w\" sizes=\"auto, (max-width: 788px) 100vw, 788px\" \/><\/a><\/figure>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Usando While La estructura While es una estructura repetitiva que permite ejecutar un bloque de instrucciones mientras que una expresi\u00f3n l\u00f3gica sea verdadera.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2408\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2409,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[232,8],"tags":[73,277,276],"class_list":["post-2408","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cursoressql","category-programacion","tag-declare-cursor","tag-repetitiva","tag-while","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2408","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2408"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2408\/revisions"}],"predecessor-version":[{"id":2413,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2408\/revisions\/2413"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2409"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2408"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2408"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2408"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}