Podcast sobre Procedimientos Almacenados en SQL Server

Kapitoly

La receta secreta de la base de datos

¿Qué es un Procedimiento Almacenado?

Los Cuatro Tipos Principales

Las Ventajas Clave

Creando y Eliminando Procedimientos

Parámetros de Entrada: Dando Instrucciones

Parámetros de Salida: Recibiendo Respuestas

Herramientas del Experto: Procedimientos del Sistema

Protegiendo y Modificando Nuestro Código

Resumen y Próximos Pasos

Parámetros de Salida

Ejecutando el Procedimiento

Parámetros Opcionales

Espiando la Base de Datos

Resumen y Despedida

Přepis

Lucía: ¡Es que es como una receta de cocina que guardas directamente en la base de datos!

Mateo: ¡Exacto! Una receta que la base de datos sabe preparar a la perfección cada vez que se la pides. ¡Sin equivocarse en los ingredientes ni en los pasos!

Lucía: Y eso, para cualquiera que esté estudiando gestión de bases de datos, cambia completamente las reglas del juego. ¡Qué increíble! Okay, hola a todos los que se unen. Estás escuchando Studyfi Podcast.

Mateo: Hoy nos sumergimos de lleno en un tema que parece intimidante pero que es súper poderoso: los procedimientos almacenados. Y como decía Lucía, la mejor forma de pensarlo es como una receta guardada.

Lucía: Me encanta esa analogía. A ver, desarróllemosla. ¿Qué significa que sea una "receta"? ¿Qué tipo de instrucciones lleva?

Mateo: ¡Buena pregunta! Un procedimiento almacenado es, en esencia, un conjunto de instrucciones de SQL a las que les damos un nombre y las guardamos directamente en el servidor de la base de datos.

Lucía: O sea, en lugar de escribir una y otra vez la misma consulta, ¿simplemente guardamos el bloque de código y lo llamamos por su nombre?

Mateo: ¡Precisamente! Imagina que tienes una tarea que haces todos los días, como generar un reporte de ventas del día. En lugar de escribir el SELECT complejo con JOINs y WHEREs cada vez, lo empaquetas en un procedimiento llamado generar_reporte_diario.

Lucía: Y luego solo ejecutas ese nombre. ¡Suena a que ahorra un montón de tiempo!

Mateo: Muchísimo. Y no solo tiempo. Encapsula tareas repetitivas, lo que hace el código más limpio y fácil de mantener. Si necesitas cambiar cómo se genera el reporte, solo modificas el procedimiento en un lugar, en lugar de buscar el código en veinte archivos diferentes.

Lucía: Okay, entiendo el concepto general. Pero vi en el temario que hay diferentes tipos. No es tan simple como "un solo tipo de receta", ¿verdad?

Mateo: No, claro. SQL Server los clasifica en cuatro categorías principales. Piénsalo como tener recetas para diferentes ocasiones.

Lucía: A ver, cuéntame. ¿Cuáles son?

Mateo: Primero, están los del **sistema**. Vienen preinstalados con SQL Server, se guardan en la base de datos "master" y sus nombres suelen empezar con sp_. Son como las herramientas que ya vienen en la cocina: te permiten ver información del sistema.

Lucía: Entendido. ¿El segundo?

Mateo: Los **locales**. Estos son los que creamos nosotros, los usuarios. Son nuestras propias recetas personalizadas, guardadas en nuestra base de datos específica. Aquí es donde pasaremos la mayor parte del tiempo hoy.

Lucía: Perfecto, los que nos importan para el examen. ¿Qué más hay?

Mateo: Luego están los **temporales**. Como su nombre indica, no son permanentes. Hay dos sabores: los locales temporales, que empiezan con un #, y solo existen para tu sesión. Cierras la conexión y desaparecen.

Lucía: ¿Y los otros?

Mateo: Los globales temporales, que empiezan con ##. Estos están disponibles para todos los usuarios conectados al servidor hasta que la sesión que los creó se cierre. Es como dejar una receta en la pizarra de la cocina para que todos la usen hoy, pero mañana ya no estará.

Lucía: Me gusta. ¿Y el último tipo?

Mateo: Son los **extendidos**. Estos son más avanzados, empiezan con xp_ y en realidad son como llamar a un programa externo desde SQL, escritos en otros lenguajes y compilados como DLLs. Hacen cosas que SQL por sí solo no puede. Para el examen, lo importante es saber que existen, pero nos centraremos en los locales.

Lucía: Okay, entonces el foco está en los locales, los que creamos nosotros. Mencionaste que ahorran tiempo, pero ¿cuáles son las otras grandes ventajas? ¿Por qué son tan importantes?

Mateo: Hay tres ventajas clave que siempre salen en los exámenes. La primera es la **centralización de la lógica**. Si varias aplicaciones usan la misma base de datos, en lugar de que cada una tenga su propia versión de una consulta, todas llaman al mismo procedimiento almacenado.

Lucía: ¡Claro! Si hay que actualizar algo, se hace en un solo lugar. ¡Súper eficiente!

Mateo: Exacto. La segunda es la **seguridad**. Puedes darle permiso a un usuario para que ejecute un procedimiento, pero no para que acceda directamente a las tablas. El procedimiento hace el trabajo sucio sin exponer los datos crudos.

Lucía: Ooh, eso es muy importante. Es como si alguien pudiera pedirte un pastel, pero no entrar a tu despensa a llevarse los ingredientes.

Mateo: ¡Perfecta analogía! Y la tercera ventaja, que es crucial, es la **reducción del tráfico de red**. En vez de enviar cientos de líneas de código SQL desde la aplicación al servidor, solo envías una línea: EXEC nombre_procedimiento.

Lucía: Menos datos viajando por la red significa más velocidad. Tiene todo el sentido.

Mateo: Correcto. El servidor ya tiene la "receta", ya la analizó y la compiló la primera vez que se ejecutó. Así que es mucho más rápido.

Lucía: ¡Manos a la obra! Quiero crear mi primera receta. ¿Cómo es la sintaxis? ¿Es complicada?

Mateo: Para nada. Es muy intuitiva. Usas la instrucción CREATE PROCEDURE o su abreviatura CREATE PROC, seguida del nombre que le quieras dar, la palabra AS y luego, simplemente, las instrucciones SQL que quieres que ejecute.

Lucía: Por ejemplo, CREATE PROC pa_crear_libros AS... y dentro va todo el código para crear mi tabla libros e insertar los datos.

Mateo: Justo así. Una cosa importante es que cuando creas el procedimiento, SQL Server revisa que la sintaxis sea correcta. Si hay un error, no lo crea. Pero, y esto es un detalle interesante, puede hacer referencia a objetos que todavía no existen. La validación de que el objeto existe se hace al *ejecutarlo*, no al crearlo.

Lucía: Ah, qué flexible. Y para llamarlo, para ejecutarlo, simplemente escribo EXEC y el nombre del procedimiento, ¿verdad?

Mateo: EXEC o EXECUTE. Ambas funcionan. Por ejemplo, EXEC pa_crear_libros;. Y ¡pum! La base de datos sigue tu receta al pie de la letra.

Lucía: ¿Y si me equivoqué y quiero borrarlo? ¿Cómo me deshago de él?

Mateo: Fácil también: DROP PROCEDURE o DROP PROC, seguido del nombre.

Lucía: ¿Pero qué pasa si intento borrar uno que no existe? Me imagino que da un error feo.

Mateo: Da un error, sí. Para evitarlo, los profesionales suelen envolverlo en una condición. Se usa una sintaxis como IF OBJECT_ID('nombre_procedimiento') IS NOT NULL DROP PROCEDURE nombre_procedimiento;.

Lucía: ¡Un truco de pro! Básicamente, "si este procedimiento existe, bórralo. Si no, no hagas nada y no me molestes con errores".

Mateo: ¡Exactamente! Es una práctica muy recomendada para que tus scripts se puedan ejecutar varias veces sin fallar.

Lucía: Okay, crear un procedimiento que siempre hace lo mismo está bien, como nuestro pa_crear_libros. Pero ¿y si quiero que mi receta sea un poco más flexible? Como... "tráeme los libros de ESTE autor en particular".

Mateo: Ahí es donde entran los **parámetros de entrada**. Son la clave para hacer que los procedimientos sean dinámicos y reutilizables. Piensa en ellos como los ingredientes que le pasas a tu receta.

Lucía: ¿Cómo se definen? Me imagino que van en la declaración del procedimiento.

Mateo: Correcto. Después del nombre del procedimiento, declaras los parámetros. Empiezan siempre con una arroba (@), seguido del nombre que le quieras dar y su tipo de dato. Por ejemplo: CREATE PROC pa_libros_autor @autor VARCHAR(30) AS....

Lucía: Y dentro del procedimiento, uso @autor como si fuera una variable normal, por ejemplo en la cláusula WHERE.

Mateo: ¡Eso es! ...WHERE autor = @autor. Cuando lo ejecutas, le pasas el valor. Por ejemplo: EXEC pa_libros_autor 'Borges';.

Lucía: ¿Y si tengo varios parámetros? Como buscar por autor Y por editorial.

Mateo: Los separas por comas. ... @autor VARCHAR(30), @editorial VARCHAR(20). Al ejecutarlo, puedes pasar los valores en el mismo orden: EXEC pa_libros_autor_editorial 'Richard Bach', 'Planeta';.

Lucía: ¿Y si no me acuerdo del orden?

Mateo: ¡No hay problema! También puedes pasarlos por nombre, y ahí el orden no importa: EXEC pa_libros_autor_editorial @editorial='Planeta', @autor='Richard Bach'. Funciona igual.

Lucía: ¡Eso es súper útil! Una última cosa sobre esto. ¿Puedo hacer que un parámetro sea opcional?

Mateo: ¡Sí! Puedes asignarle un valor por defecto al declararlo. Por ejemplo: @autor VARCHAR(30) = 'Richard Bach'. Si al ejecutar el procedimiento no le pasas un valor para @autor, usará 'Richard Bach' automáticamente.

Lucía: Fantástico. Eso le da muchísima flexibilidad a nuestras "recetas".

Mateo: Ahora, a veces no solo quieres darle ingredientes a tu receta, sino que quieres que la receta te devuelva un resultado específico. No una tabla de datos, sino un valor único.

Lucía: A ver, un ejemplo. ¿Como si le pidiera que me calcule el precio promedio de los libros de un autor y me devuelva solo ese número?

Mateo: ¡Perfecto ejemplo! Para eso se usan los **parámetros de salida**. Se declaran de forma muy parecida a los de entrada, pero añadiendo la palabra clave OUTPUT al final.

Lucía: Entonces sería algo como ... @promedio DECIMAL(6,2) OUTPUT AS...

Mateo: Exactamente. Dentro del procedimiento, haces el cálculo y asignas el resultado a esa variable. Por ejemplo: SELECT @promedio = AVG(precio) FROM libros WHERE....

Lucía: Ok, la parte de crear el procedimiento está clara. Pero, ¿cómo lo ejecuto? ¿Cómo "atrapo" ese valor que me devuelve?

Mateo: Es un poco diferente. Primero, tienes que declarar una variable en tu script, fuera del procedimiento, para recibir el valor. Por ejemplo: DECLARE @mi_promedio DECIMAL(6,2);.

Lucía: Entiendo, preparo un recipiente para el resultado.

Mateo: Eso es. Luego, al ejecutar el procedimiento, le pasas esa variable también con la palabra OUTPUT. Así: EXEC pa_calcular_promedio @autor='Borges', @promedio=@mi_promedio OUTPUT;.

Lucía: Y después de esa línea, ¿la variable @mi_promedio ya contiene el valor calculado?

Mateo: ¡Correcto! Ya puedes hacer un SELECT @mi_promedio; para verlo o usarlo en otros cálculos. Es una forma muy limpia de que un procedimiento te devuelva valores concretos, como un total, un promedio o un conteo.

Lucía: Antes mencionaste los procedimientos del sistema, los que empiezan con sp_. Suenan como herramientas secretas. ¿Cuáles son los más útiles que deberíamos conocer?

Mateo: No son secretos, ¡pero sí muy poderosos! Hay algunos que usarás constantemente. El primero es sp_help. Si le pasas el nombre de cualquier objeto, como una tabla o un procedimiento, te da un resumen completo de su estructura.

Lucía: ¡Wow! Como una radiografía del objeto. EXEC sp_help 'pa_libros_autor'; me diría qué parámetros tiene, cuándo se creó...

Mateo: Exacto, es súper útil para explorar una base de datos que no conoces. Otro indispensable es sp_helptext. Este va un paso más allá: te muestra el código fuente completo con el que se creó un procedimiento.

Lucía: ¡Eso es genial para aprender o para entender qué hace un procedimiento que no escribiste tú! Espera, ¿y si no quiero que vean mi código? ¿Se puede proteger?

Mateo: ¡Sí! Y eso nos lleva a otro tema. Pero antes, déjame mencionarte dos más. sp_stored_procedures te lista todos los procedimientos de la base de datos actual.

Lucía: Útil para tener una visión general.

Mateo: Y sp_depends, que es fascinante. Le pasas el nombre de un procedimiento y te dice de qué otros objetos (como tablas) depende. O, al revés, le pasas el nombre de una tabla y te dice qué procedimientos la están usando.

Lucía: ¡Eso es vital! Para saber si al modificar una tabla vas a romper algún procedimiento. ¡Qué bueno saber que existen estas herramientas!

Mateo: Volviendo a tu pregunta sobre proteger el código. Sí, se puede. Al crear el procedimiento, puedes añadir la opción WITH ENCRYPTION justo antes de la palabra AS.

Lucía: CREATE PROC mi_procedimiento WITH ENCRYPTION AS...

Mateo: Correcto. Una vez que haces eso, el texto del procedimiento se encripta en las tablas del sistema. Si alguien intenta usar sp_helptext sobre él, recibirá un mensaje diciendo que el objeto está encriptado.

Lucía: Es como guardar tu receta en una caja fuerte. Nadie puede ver los ingredientes secretos.

Mateo: ¡Exactamente! Es una medida de seguridad para proteger la lógica de negocio o algoritmos complejos.

Lucía: Ahora, ¿qué pasa si necesito cambiar un procedimiento? ¿Tengo que hacer DROP y CREATE de nuevo? Eso parece un poco drástico.

Mateo: No es necesario. Para eso existe ALTER PROCEDURE. La sintaxis es idéntica a CREATE PROCEDURE, pero usa la palabra ALTER. Te permite modificar un procedimiento existente sin tener que eliminarlo y volver a crearlo.

Lucía: Y lo más importante, conserva los permisos que ya tenía asignados, ¿verdad? Si lo borro y lo creo de nuevo, perdería los permisos.

Mateo: ¡Punto clave! Usar ALTER es la forma correcta y segura de modificar procedimientos en un entorno de producción, precisamente porque mantiene los permisos y las dependencias intactas.

Lucía: Entonces, si encripté un procedimiento y luego necesito editarlo, ¿puedo usar ALTER y quitarle la cláusula WITH ENCRYPTION?

Mateo: ¡Sí! Puedes usar ALTER para añadir o quitar la encriptación, además de cambiar el código. Es completamente flexible.

Lucía: A ver si lo he entendido todo. Un procedimiento almacenado es un bloque de código SQL guardado en el servidor, que podemos ejecutar por su nombre.

Mateo: Correcto. Ahorra tiempo, mejora el rendimiento al reducir el tráfico de red, centraliza la lógica de negocio y aumenta la seguridad.

Lucía: Podemos hacerlos dinámicos usando parámetros de entrada para darles datos, e incluso hacer que nos devuelvan valores específicos con parámetros de salida usando OUTPUT.

Mateo: Perfecto. Y no olvides que tenemos herramientas como sp_help y sp_helptext para inspeccionarlos, y podemos proteger nuestro código con WITH ENCRYPTION.

Lucía: Y si necesitamos cambiar algo, la palabra mágica es ALTER PROCEDURE, no DROP y CREATE.

Mateo: ¡Lo tienes! Has resumido perfectamente una hora de clase en 30 segundos. Los procedimientos almacenados son una de las herramientas más fundamentales y potentes en la gestión de bases de datos.

Lucía: Pues después de esta charla, ya no parecen tan intimidantes. ¡Se sienten más como un superpoder! Gracias, Mateo.

Mateo: Un placer, Lucía. La clave es practicar. Creen procedimientos simples, luego añadan parámetros... y verán lo útiles que son.

Lucía: Gran consejo. Bueno, es hora de tomar un pequeño respiro. Al volver, vamos a cambiar de marcha y hablaremos de otro pilar de las bases de datos. ¡No se vayan!

Lucía: ¡Wow! Entonces los procedimientos almacenados que vimos antes son solo el comienzo. Pueden hacer mucho más que solo mostrar datos.

Mateo: ¡Exactamente! Y eso nos lleva a nuestro último tema de hoy... los parámetros de salida. ¡Aquí es donde la magia realmente sucede!

Lucía: ¿Parámetros de... salida? Suena como una puerta de emergencia para datos.

Mateo: ¡Es una buena analogía! Piensa en ellos como pequeñas bolsas vacías que le das a un procedimiento. Le pides que haga sus cálculos y que, por favor, ponga los resultados en esas bolsas para que los puedas usar después.

Lucía: Ah, ¡interesante! Entonces, no solo te *muestra* el resultado, te lo *devuelve* en una variable.

Mateo: ¡Bingo! Mira este ejemplo. Creamos un procedimiento llamado pa_autor_sumaypromedio. Le daremos un autor, y nos devolverá la suma y el promedio de los precios de sus libros.

Lucía: ¿Y cómo sabe cuáles son las “bolsas” para los resultados?

Mateo: Usamos la palabra clave output al declararlos. Fíjate en el código: @suma decimal(6,2) output y @promedio decimal(6,2) output. Eso le dice a SQL: "Oye, estos no son para recibir datos, son para enviarlos de vuelta".

Lucía: Entendido. Es como pedir una pizza y también el cambio. ¿Cómo lo ejecutamos?

Mateo: Primero, declaramos nuestras "bolsas" o variables para guardar los resultados. En este caso, @s para la suma y @p para el promedio. Luego, usamos exec para llamar al procedimiento, le pasamos el autor, como 'Richard Bach', y nuestras variables con la palabra output.

Lucía: Y después... ¿mágicamente las variables @s y @p tienen los valores?

Mateo: ¡Como por arte de magia! Después del exec, hacemos un select a esas variables y... ¡listo! Vemos el total y el promedio solo para los libros de Richard Bach. Es súper eficiente.

Lucía: ¡Qué genial! Veo que también lo probaron con 'Borges' y 'Mario Molina'. Funciona de maravilla para obtener resúmenes rápidos de datos específicos sin tener que escribir la consulta de suma y promedio cada vez.

Mateo: Exacto. Reutilización y eficiencia. Ese es el superpoder de los procedimientos almacenados.

Lucía: Ok, pero veo algo interesante en la definición del procedimiento... @autor varchar(30)='%'. ¿Qué hace ese signo de porcentaje?

Mateo: ¡Buena observación! Ese es un valor por defecto. Si no le especificamos un autor al procedimiento, usará el %, que en SQL es un comodín que significa "cualquier cosa".

Lucía: ¡O sea que si no le digo un autor, me dará los datos de TODOS los autores!?

Mateo: ¡Precisamente! Pero hay un pequeño truco. Como estamos omitiendo el primer parámetro (@autor), tenemos que nombrar los otros al ejecutarlo. Por eso la sintaxis se ve así: exec pa_autor_sumaypromedio @suma=@s3 output, @promedio=@p3 output.

Lucía: Ah, claro. Le decimos explícitamente qué variable corresponde a qué parámetro. Es como decirle a un GPS la dirección exacta en lugar de solo "llévame al centro".

Mateo: ¡Perfecto! Así no hay confusiones. Y el resultado es la suma y el promedio de los precios de *toda* la tabla de libros.

Lucía: Esto es increíblemente poderoso. Pero, ¿qué pasa si olvido cómo se llama un procedimiento o qué parámetros necesita? ¿Tengo que buscar el archivo original donde lo creé?

Mateo: ¡Para nada! SQL tiene sus propios procedimientos espía, por así decirlo. Son los procedimientos del sistema, y usualmente empiezan con sp_.

Lucía: ¿Procedimientos espía? ¡Suena divertido! ¿Cuáles son?

Mateo: Por ejemplo, sp_help te da una lista de todos los objetos en tu base de datos, incluyendo tablas y procedimientos. Si quieres detalles de uno en específico, usas sp_help seguido del nombre, como sp_help pa_autor_sumaypromedio.

Lucía: ¡Wow! Y te dice los nombres de los parámetros, el tipo, el orden... ¡todo!

Mateo: ¡Y aún hay más! Si quieres ver el código fuente exacto con el que se creó, usas sp_helptext. Es como tener rayos X para ver el esqueleto de tus procedimientos.

Lucía: ¡Esto es como tener los trucos secretos del juego! Ya no hay excusa para no saber qué hace cada cosa en la base de datos.

Lucía: Pues, Mateo, ha sido una sesión increíble. Hemos pasado de consultas simples a crear y ejecutar procedimientos complejos con parámetros de entrada, salida y hasta opcionales.

Mateo: Así es. El punto clave aquí es que los procedimientos almacenados son herramientas reutilizables y muy potentes. Nos ayudan a mantener nuestro código organizado, seguro y, sobre todo, muy eficiente.

Lucía: Un resumen perfecto. Y con esos súper poderes de los procedimientos espía del sistema, cerramos nuestro capítulo de hoy sobre bases de datos.

Mateo: Ha sido un placer, Lucía. ¡Espero que a todos les sirva para sus estudios!

Lucía: ¡Seguro que sí! Muchísimas gracias a ti, Mateo, por compartir tu conocimiento. Y a todos ustedes que nos escuchan, gracias por acompañarnos en otro episodio de Studyfi Podcast. ¡Hasta la próxima!

Mateo: ¡Adiós a todos!