Este post lo hago para ayuda de memoria y también para los que necesiten una mano sobre Transact-SQL.
Puede crear procedimientos almacenados mediante la instrucción CREATE PROCEDURE de Transact-SQL.
Cuando cree un procedimiento almacenado, deberá especificar lo siguiente:
No es recomendable utilizar el prefijo sp_ para crear procedimientos almacenados. SQL Server siempre busca procedimientos almacenados que empiezan con sp_ siguiendo este orden:
Por lo tanto, aunque en la base de datos actual pueda haber un procedimiento almacenado creado por el usuario y que empiece por sp_, en primer lugar se comprueba siempre la base de datos master incluso cuando el procedimiento esté calificado con el nombre de la base de datos.
Si algún procedimiento almacenado creado por un usuario tiene el mismo nombre que un procedimiento almacenado del sistema, el que haya creado el usuario no llegará a ejecutarse
los procedimientos almacenados temporales, tanto privados como globales, se pueden crear agregando los prefijos # y ## delante del nombre del procedimiento. # denota un procedimiento almacenado temporal local; ## denota un procedimiento almacenado temporal global. Estos procedimientos dejan de existir cuando se cierra SQL Server.
(Similar notación se utiliza para las tablas temporales: #nomb_tabla_local , ##nomb_tabla_global)
Declare
Las variables se declaran en el cuerpo de un proceso por lotes o procedimiento con la instrucción DECLARE, y se les asignan valores con una instrucción SET o SELECT. Después de su declaración, todas las variables se inicializan con NULL (nulo). @var_local , @@var_global
BEGIN...END
IF...ELSE
CREATE PROCEDURE #busco_art
@cod_art INT
Puede crear procedimientos almacenados mediante la instrucción CREATE PROCEDURE de Transact-SQL.
Cuando cree un procedimiento almacenado, deberá especificar lo siguiente:
- Todos los parámetros de entrada y de salida del lote o del procedimiento que realiza la llamada.
- Las instrucciones de programación que realicen operaciones en la base de datos, incluidas las llamadas a otros procedimientos.
No es recomendable utilizar el prefijo sp_ para crear procedimientos almacenados. SQL Server siempre busca procedimientos almacenados que empiezan con sp_ siguiendo este orden:
- El procedimiento almacenado en la base de datos master.
- El procedimiento almacenado basándose en cualquiera de los calificadores especificados (nombre o propietario de la base de datos.
- El procedimiento almacenado mediante el propietario dbo, si no se especifica otro.
Por lo tanto, aunque en la base de datos actual pueda haber un procedimiento almacenado creado por el usuario y que empiece por sp_, en primer lugar se comprueba siempre la base de datos master incluso cuando el procedimiento esté calificado con el nombre de la base de datos.
Si algún procedimiento almacenado creado por un usuario tiene el mismo nombre que un procedimiento almacenado del sistema, el que haya creado el usuario no llegará a ejecutarse
los procedimientos almacenados temporales, tanto privados como globales, se pueden crear agregando los prefijos # y ## delante del nombre del procedimiento. # denota un procedimiento almacenado temporal local; ## denota un procedimiento almacenado temporal global. Estos procedimientos dejan de existir cuando se cierra SQL Server.
(Similar notación se utiliza para las tablas temporales: #nomb_tabla_local , ##nomb_tabla_global)
Declare
Las variables se declaran en el cuerpo de un proceso por lotes o procedimiento con la instrucción DECLARE, y se les asignan valores con una instrucción SET o SELECT. Después de su declaración, todas las variables se inicializan con NULL (nulo). @var_local , @@var_global
DECLARE @nombre varchar(30) SET @nombre = 'Maria'
CREATE
PROCEDURE ver_articulos AS
SELECT descripcion, cantidad, precio_compra
FROM articulos
WHERE cantidad < 100
GO
CREATE PROCEDURE #busco_articulo
@cod_art INT
AS
@cod_art INT
AS
SELECT *
FROM articulos
WHERE
codigo_articulo = @cod_art
GO
EXEC
#busco_articulo @cod_art = 2
GO
Para mostrar el
texto utilizado para crear el procedimiento, ejecute sp_helptext en la
base de datos en que existe el procedimiento, con el nombre del procedimiento
como parámetro. sp_helptext ver_articulos
Palabras claves de Transact-SQL
BEGIN...END
Encierra un conjunto de instrucciones Transact-SQL de forma que se pueda ejecutar. BEGIN y END son palabras clave del lenguaje de control de flujo.
IF...ELSE
Impone condiciones en la ejecución
de una instrucción Transact-SQL. La instrucción Transact-SQL que sigue a una
palabra clave IF y a su condición se ejecuta si la condición se satisface
(cuando la expresión booleana devuelve TRUE). La palabra clave opcional ELSE
introduce una instrucción Transact-SQL alternativa que se ejecuta cuando la
condición IF no se satisface (cuando la expresión booleana devuelve FALSE).
CREATE PROCEDURE #busco_art
@cod_art INT
AS
IF(SELECT AVG(precio_compra)
FROM articulos
WHERE codigo_articulo < @cod_art) < 15
BEGIN
PRINT 'Los siguientes articulos en oferta:'
PRINT ' '
SELECT SUBSTRING(descripcion, 1, 10)AS Articulo,precio_venta
BEGIN
PRINT 'Los siguientes articulos en oferta:'
PRINT ' '
SELECT SUBSTRING(descripcion, 1, 10)AS Articulo,precio_venta
FROM articulos
WHERE codigo_articulo < @cod_art
END
exec #busco_art @cod_art = 5
WHILE
BREAK
Observaciones
WHERE codigo_articulo < @cod_art
END
exec #busco_art @cod_art = 5
WHILE
Establece una condición para la
ejecución repetida de una instrucción o bloque de instrucciones de SQL. Las
instrucciones se ejecutan repetidamente mientras la condición especificada sea
verdadera. Se puede controlar la ejecución de instrucciones en el bucle WHILE
con las palabras clave BREAK y CONTINUE.
Hace que se salga del bloque WHILE más interno. Se ejecutan
las instrucciones que aparecen después de la palabra clave END, que marca el
final del bucle.
CONTINUE
Hace que se reinicie el bucle WHILE y omite las
instrucciones que haya después de la palabra clave CONTINUE.
Observaciones
Si dos o más bucles WHILE están
anidados, la instrucción BREAK interna sale al siguiente bucle más externo.
Primero se ejecutan todas las instrucciones que haya después del final del
bucle interno y, a continuación, se reinicia el siguiente bucle más externo.
WHILE (SELECT AVG(precio_venta) FROM articulos) < 10
BEGIN
UPDATE articulos
SET precio_compra = precio_compra * 2
SELECT MAX(precio_venta)AS maximo
FROM articulos
FROM articulos
IF (SELECT MAX(precio_venta) FROM articulos) > 35
BREAK
ELSE
CONTINUE
END
Esta es una breve lista de recomendaciones básicas que ayudan mucho al momento de trabajar con stored procedures en SQL Server.
Como últimamente he utilizado mucho SQL Server 2008 R2 puede haber alguna sugerencia que solo aplique a dicha versión.
1) Para facilitar la lectura utilice una identación adecuada.
2) Agregue comentarios en la lógica compleja y utilice el wizard de creación de objetos (stored procedures en este caso) para completar los campos sugeridos (autor, fecha de creación, breve descripción). Recuerde utilizar Ctrl+Shift+M para especificar los valores del template.
3) Es buena práctica escribir las palabras reservadas en mayúsculas (por ej. SELECT, RTRIM, FROM, DELETE, etc..) porque facilitan mucho la lectura del código.
4) Escriba el nombre del stored procedure de forma correctamente calificada ( por ej. [dbo]. EmployeeSalaryCalc )
5) En lo posible siempre declarar e inicializar las variables al comienzo del código del stored procedure, tratar de usar la cantidad de variables mínimas necesarias.
6) No escriba el nombre del stored procedure comenzando con el prefijo “sp_”, esto es reservado para los sotred procedures de sistema de SQL Server. Cuando se hace la solicitud de un stored procedure que comienza con ese prefijo el motor de base de datos intenta buscar primero en la base de datos “master” y luego en las demas.
7) Agregue la opción de SET NOCOUNT ON al inicio del procedimiento para evitar el mensaje innecesario de número de filas afectadas.
8) Utilice tablas temporales solamente cuando es necesario. Los stored procedure suelen utilizar un plan de ejecución, almacenado en caché, para aumentar el rendimiento, cuando se utilizan tablas temporales se requiere hacer la compilación en cada ejecución y no se puede aprovechar el plan de ejecución.
9) Trate de devolver siempre las columnas necesarias, es muy recomendable evitar sentencias del tipo “SELECT * FROM”.
10) Utilice cursores solo cuando es necesario. Los cursores requieren más memoria y por tanto se degrada el rendimiento del stored procedure. En algunos casos se puede utilizar la variable de tabla y el bucle WHILE para iterar sobre un el conjunto de resultados.
11) Es buena práctica asignar el valor por defecto para cada parámetro y siempre establecer el tamaño correcto de las variables (tanto de retorno como internas). Por ejemplo si en la tabla un campo determinado tiene largo 100 (Username (varchar(100)) y en el procedimiento se utiliza como varchar(200) se puede producir un error en tiempo de ejecución del tipo "error de cadena truncada" si se está frente a un INSERT o UPDATE de dicho campo.
12) Utilice la sentencia Try para capturar adecuadamente los errores en tiempo de ejecución dentro del stored procedure y hacer un manejo acorde en dicha situación.
13) Ante consultas complejas y que se utilizan en varias ocasiones, tenga en cuenta crear vistas para un manejo más adecuado.
14) Evite utilizar sub-consultas (SELECT A.column1 FROM (SELECT data1 FROM) as A …), utilice la sentencia INNER JOIN siempre que sea posible.
15) Trate de evitar la condición de filtrado en la cláusula WHERE si puede ser escrito al momento de utilizar la sentencia de JOIN.
16) Utilice la sentencia SELECT TOP 1 para la comprobación/obtención de un dato en base a una condición.
Ej. en lugar de : SELECT @ name = nombre FROM Empleados WHERE nombre LIKE '%Rob%'
Si se devuelven múltiples registros ocasiona un error en momento de ejecución.
SELECT TOP 1 @ name = nombre FROM Empleados WHERE nombre LIKE '%Rob%'
17) En los caso donde se puede aplicar una instrucción CASE es mejor que el anidamiento de sentencias IF-ELSE.
18) Consultas Dinámicas: trate de reducir al mínimo el uso de las consultas dinámicas.
Si está utilizando una consulta dinámica similar a “SELECT * FROM mydb.dbo.emp WHERE empid = @ EmpId”, entonces no hay problema. En este caso se proporciona un valor para el parámetro @EmpId y el motor no tiene que generar o recargar un nuevo plan de ejecución. En cambio si se utiliza una consulta del tipo @query = “SELECT * FROM emp donde empid =” + @ EmpId esto por defecto no admite un plan de ejecución predeterminado y cargado en el motor de base de datos.
19) Utilice las instrucciones ORDER BY y DISTINCT solo cuando se requieran. Caso contrario el motor de SQL Server obtendrá el primer resultado y lo hará de nuevo si requiere utilizar algunas de estas instrucciones.
20) Utilice los índices correctos en las columnas de cada tabla. No es buena práctica crear índices en las columnas que no se utilizan en las cláusulas WHERE. Cuando se crean índices extras se puede requerir un ida y vuelta extra del motor de base de datos para consultar el resultado
20 tips para escribir stored procedures en SQL Server
Esta es una breve lista de recomendaciones básicas que ayudan mucho al momento de trabajar con stored procedures en SQL Server.
Como últimamente he utilizado mucho SQL Server 2008 R2 puede haber alguna sugerencia que solo aplique a dicha versión.
1) Para facilitar la lectura utilice una identación adecuada.
2) Agregue comentarios en la lógica compleja y utilice el wizard de creación de objetos (stored procedures en este caso) para completar los campos sugeridos (autor, fecha de creación, breve descripción). Recuerde utilizar Ctrl+Shift+M para especificar los valores del template.
3) Es buena práctica escribir las palabras reservadas en mayúsculas (por ej. SELECT, RTRIM, FROM, DELETE, etc..) porque facilitan mucho la lectura del código.
4) Escriba el nombre del stored procedure de forma correctamente calificada ( por ej. [dbo]. EmployeeSalaryCalc )
5) En lo posible siempre declarar e inicializar las variables al comienzo del código del stored procedure, tratar de usar la cantidad de variables mínimas necesarias.
6) No escriba el nombre del stored procedure comenzando con el prefijo “sp_”, esto es reservado para los sotred procedures de sistema de SQL Server. Cuando se hace la solicitud de un stored procedure que comienza con ese prefijo el motor de base de datos intenta buscar primero en la base de datos “master” y luego en las demas.
7) Agregue la opción de SET NOCOUNT ON al inicio del procedimiento para evitar el mensaje innecesario de número de filas afectadas.
8) Utilice tablas temporales solamente cuando es necesario. Los stored procedure suelen utilizar un plan de ejecución, almacenado en caché, para aumentar el rendimiento, cuando se utilizan tablas temporales se requiere hacer la compilación en cada ejecución y no se puede aprovechar el plan de ejecución.
9) Trate de devolver siempre las columnas necesarias, es muy recomendable evitar sentencias del tipo “SELECT * FROM”.
10) Utilice cursores solo cuando es necesario. Los cursores requieren más memoria y por tanto se degrada el rendimiento del stored procedure. En algunos casos se puede utilizar la variable de tabla y el bucle WHILE para iterar sobre un el conjunto de resultados.
11) Es buena práctica asignar el valor por defecto para cada parámetro y siempre establecer el tamaño correcto de las variables (tanto de retorno como internas). Por ejemplo si en la tabla un campo determinado tiene largo 100 (Username (varchar(100)) y en el procedimiento se utiliza como varchar(200) se puede producir un error en tiempo de ejecución del tipo "error de cadena truncada" si se está frente a un INSERT o UPDATE de dicho campo.
12) Utilice la sentencia Try para capturar adecuadamente los errores en tiempo de ejecución dentro del stored procedure y hacer un manejo acorde en dicha situación.
13) Ante consultas complejas y que se utilizan en varias ocasiones, tenga en cuenta crear vistas para un manejo más adecuado.
14) Evite utilizar sub-consultas (SELECT A.column1 FROM (SELECT data1 FROM) as A …), utilice la sentencia INNER JOIN siempre que sea posible.
15) Trate de evitar la condición de filtrado en la cláusula WHERE si puede ser escrito al momento de utilizar la sentencia de JOIN.
16) Utilice la sentencia SELECT TOP 1 para la comprobación/obtención de un dato en base a una condición.
Ej. en lugar de : SELECT @ name = nombre FROM Empleados WHERE nombre LIKE '%Rob%'
Si se devuelven múltiples registros ocasiona un error en momento de ejecución.
SELECT TOP 1 @ name = nombre FROM Empleados WHERE nombre LIKE '%Rob%'
17) En los caso donde se puede aplicar una instrucción CASE es mejor que el anidamiento de sentencias IF-ELSE.
18) Consultas Dinámicas: trate de reducir al mínimo el uso de las consultas dinámicas.
Si está utilizando una consulta dinámica similar a “SELECT * FROM mydb.dbo.emp WHERE empid = @ EmpId”, entonces no hay problema. En este caso se proporciona un valor para el parámetro @EmpId y el motor no tiene que generar o recargar un nuevo plan de ejecución. En cambio si se utiliza una consulta del tipo @query = “SELECT * FROM emp donde empid =” + @ EmpId esto por defecto no admite un plan de ejecución predeterminado y cargado en el motor de base de datos.
19) Utilice las instrucciones ORDER BY y DISTINCT solo cuando se requieran. Caso contrario el motor de SQL Server obtendrá el primer resultado y lo hará de nuevo si requiere utilizar algunas de estas instrucciones.
20) Utilice los índices correctos en las columnas de cada tabla. No es buena práctica crear índices en las columnas que no se utilizan en las cláusulas WHERE. Cuando se crean índices extras se puede requerir un ida y vuelta extra del motor de base de datos para consultar el resultado
Comentarios
Publicar un comentario