Trucos de Sql Server Crear un Stored Procedure

Para crear un Stored podemos utilizar el Enterprise Manager o el Query Analizer. Si lo hacemos por el Enterprise Manager, encima de la base de datos, desplegaremos la carpeta de storeds, botn derecho y "New Stored Procedure" El Enterprise Manager por defecto pone: CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS En Procedure Name colocamos el nombre del Stored que deseamos, y a continuacin del AS las consultas que queramos: CREATE PROCEDURE DIC_FORO AS SET NOCOUNT ON SELECT Campos FROM Tabla With(NoLock) SET NOCOUNT OFF GO Los Storeds admiten parmetros. Sirven para poder realizar querys ms precisas. Por ejemplo: CREATE PROCEDURE DIC_FORO ( @IdUsuario int=0 ) AS SET NOCOUNT ON SELECT * FROM Usuario With(NoLock)

WHERE Usuario=@IdUsuario

SET NOCOUNT OFF GO

Tambin podemos utilizar parmetros de salida, para retornar datos. Para ello declararemos el parmetro como Output.

A.Utilizar BREAK y CONTINUE con IFELSE y WHILE anidados

En el ejemplo siguiente, si el precio de venta promedio de un producto es inferior a $300, el bucle WHILE dobla los precios y, a continuacin, selecciona el precio mximo. Si el precio mximo es menor o igual que $500, el bucle WHILE se reinicia y vuelve a doblar los precios. Este bucle contina doblando los precios hasta que el precio mximo es mayor que $500, despus de lo cual sale del bucle WHILE e imprime un mensaje. Transact-SQL USE AdventureWorks2012; GO WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 BEGIN UPDATE Production.Product SET ListPrice = ListPrice * 2 SELECT MAX(ListPrice) FROM Production.Product IF (SELECT MAX(ListPrice) FROM Production.Product) > $500 BREAK ELSE CONTINUE END PRINT 'Too much for the market to bear';
DECLARE @CONTADOR INT SET @CONTADOR = 1 WHILE @CONTADOR < 1000 BEGIN INSERT INTO TABLE VALUES('XXX') CONTINUE END

CONSULTA: IF NOT EXISTS(Select nombre from persona wnere nombre = @nombre) Begin

Insert into persona (nombre) values (@nombre) End

[SQL] Por qu utilizar Tablas Temporales Locales, Globales o Variables de Tabla?.

12062009

10 Votes

Saludos amigos en esta ocasin leyendo me encontr con un tema muy interesante acerca de las tablas temporales en SQL, les dejo lo que traduje del tema con una explicacin y ejemplo de como crear tablas temporales locales, globales y variables de tabla, dentro del Motor de Base de Datos de SQL Server 2008.

Tablas Temporales.
Las tablas temporales son consideradas tablas regulares, y estas se almacenan automticamente en la base de datos de tempdb. Las tablas temporales se pueden usar en los siguientes escenarios: Como alternativa a los cursores: por ejemplo, en lugar de utilizar un cursor de Transact-SQL para recorrer un conjunto de resultados y realizar tareas basadas en cada fila, en su lugar puede utilizar una tabla temporal. Usando un bucle WHILE, puede recorrer cada fila de la tabla y realizar la accin de la fila especificada, posteriormente debe eliminar la fila de la tabla temporal. Como almacenamiento incremental de conjuntos de resultados: Por ejemplo, imaginemos que tiene una sola consulta SELECT que realiza una combinacin JOINcon diez tablas. A veces las consultas con varias combinaciones JOINS pueden funcionar de manera incorrecta. Una tcnica para intentar es la de fraccionar o fragmentar una consulta grande en consultas ms pequeas. Si usamos tablas temporales, podemos crear conjuntos de resultados intermedios basados en consultas de menor

tamao, en lugar de intentar ejecutar una consulta nica que sea demasiado grande y de combinaciones mltiples multi-joined. Como temporal en la sobrecarga baja de bsqueda de tabla: Por ejemplo, imagine que usted est utilizando una consulta que tarda varios segundos en ejecutarse, pero slo muestra un pequeo conjunto de resultados, el cual desea utilizar en varias reas de su procedimiento almacenado, pero cada vez que se llama se incurre en el tiempo de ejecucin de la consulta general. Para resolver esto, puede ejecutar la consulta una sola vez en el procedimiento, llenando una tabla temporal, de esta manera se puede hacer referencia a la tabla temporal en varios lugares en su cdigo, sin incurrir en una sobrecarga de resultados adicional. Existen dos tipos de tablas temporales: Globales y Locales. Las tablas temporales Locales se escriben anteponiendo el smbolo # y tablas

temporales Globales con el doble smbolo ##.

Tablas temporales Locales.

Las tablas temporales Locales estn disponibles para usarse por cada conexin actual del usuario que los cree. Varias conexiones pueden crear una tabla temporal con mismo nombre, esto solo para para tablas temporales Locales sin causar conflictos. La representacin interna de la tabla local tiene un nombre nico, para no estar en conflicto con otras tablas temporales con el mismo nombre creado por otras conexiones en la tempdb. Las tablas temporales locales son eliminadas con el comando DROP o se eliminan automticamente de memoria cuando se cierra la conexin del usuario. Creando la tabla temporal Local: CREATE TABLE #ProductosResumen ( idProducto int NOT NULL PRIMARY KEY, nombre varchar(75) NULL, precio smallmoney NULL ); Insertando datos en la tabla temporal global. INSERT #ProductosResumen (idProducto, nombre , precio) SELECT id,nombre, precio FROM dbo.DEMO_PRODUCTO ORDER BY nombre;

Seleccionando los datos de la tabla temporal. --Seleccionando los datos de la tabla temporal SELECT * FROM #ProductosResumen;

-- Resumen de precios de la tabla temporal SELECT AVG(precio) FROM #ProductosResumen;

Resultado.

Eliminando la tabla. -- Eliminando la tabla temporal DROP TABLE #ProductosResumen;

Nota: Para que el ejemplo funcione deben de ejecutar en un solo bloque de instrucciones todo el cdigo que coloque anteriormente. Para este caso yo llene mi tabla temporal con datos de una tabla que ya tenia creada en la base de datos, ustedes pueden utilizar un insert normal.

Tablas Temporales Globales.

Las tablas temporales Globales tienen un alcance diferente al de las tablas temporalesLocales. Una vez que una conexin crea una tabla temporal Global, cualquier usuario con permisos adecuados sobre la base de datos puede acceder a la tabla. A diferencia de tablas temporales Locales, no se

pueden crear versiones simultneas de una tabla temporal Global , ya que esto generar un conflicto
de nombres. Las tablas temporales Globales de eliminan explcitamente de SQL Server ejecutandoDROP TABLE. Tambin se eliminan automticamente despus de que se cierra la conexin que la creo, la tabla

temporal Global no es referenciada por otras conexiones, pero es muy raro ver que se utilicen tablas temporales Globales en bases de datos en produccin. Es importante considerar cuando una tabla va o debe ser compartida a travs de conexiones, se debe crear una tabla real, en lugar de una tabla temporal Global. No obstante, SQL Server ofrece esto como una opcin. Creando la tabla temporal Global: --Creando la tabla temporal Global CREATE TABLE ##Roles ( idRol int NOT NULL PRIMARY KEY, nombre varchar(30) NULL, activo bit NULL ); Insertando y seleccionando datos de la tabla temporal global: -- Insertando datos en la tabla temporal global INSERT INTO ##Roles VALUES(1,'Administrador',1), (2,'Supervisor',1), (3,'Programador',0)

-- Seleccionando los datos de la tabla temporal global SELECT * FROM ##Roles; Resultado:

Eliminando la tabla temporal global desde la conexin original que la creo. -- Eliminando la tabla temporal global

DROP TABLE

##Roles;

Variables de Tabla.
Por otro lado tenemos las Variables de Tabla que son un tipo de datos que puede ser utilizados en un lote Transact-SQL (Batch), procedimiento almacenado o funcin; estas variables de tabla son creado y definidas de forma similar a una tabla, slo con un alcance de vida estrictamente definido. Las Variables de tabla suelen ser buenos reemplazos de tablas temporales siempre y

cuando el conjunto de datos es pequeo.

Razones para usar las variables de tabla:

Duracin o alcance. La duracin de la variable de tabla slo vive durante la ejecucin del lote, funcin, o procedimiento almacenado. Tiempos de bloqueo ms cortos. Por el estrecho alcance o tiempo de vida. Menos re compilaciones cuando se usa en los procedimientos almacenados. Como se menciono anteriormente, hay inconvenientes para utilizar las variables de tabla. El

rendimiento de las variable de tabla se ve afectado cuando el resultado es demasiado grande o cuando los datos de la columna de cardinalidad son fundamentales para la optimizacin del proceso de consulta.
La sintaxis para crear una variable de tabla es similar a la de crear una tabla normal, se utiliza la palabra clave DECLARE y el nombre de tabla, anteponiendo el smbolo @: DECLARE @TableName TABLE

(column_name <data_type> [ NULL | NOT NULL ] [ ,...n ] ) Creando una variable de tabla: -- Creando la variable de tipo tabla. DECLARE @EstatusUsuarios TABLE ( idEstatus int NOT NULL PRIMARY KEY, nombre varchar(30) NULL ) Insertando y seleccionando datos de la variable tabla: -- Insertando en la variable de tipo tabla. INSERT INTO @EstatusUsuarios VALUES (1,'Activo'), (2,'Inactivo'),

(3,'Bloqueado')

-- Consultando datos de la variable de tipo tabla. SELECT * FROM @EstatusUsuarios; Al terminar la ejecucin del batch o bloque de instrucciones se eliminara la variable tabla, o si colocamos una instruccin GO automticamente se eliminara y no la podremos utilizar como los muestro en el siguiente ejempl, recuerde que los ejemplo para claridad los coloque separados, pero los debe de ejecutar completos como lo hago abajo para el caso de la variables de tabla: -- Creando la variable de tipo tabla. DECLARE @EstatusUsuarios TABLE ( idEstatus int NOT NULL PRIMARY KEY, nombre varchar(30) NULL )

-- Insertando en la variable de tipo tabla. INSERT INTO @EstatusUsuarios VALUES (1,'Activo'), (2,'Inactivo'), (3,'Bloqueado')

-- Consultando datos de la variable de tipo tabla. SELECT * FROM @EstatusUsuarios;

-- Al ejecutarse el go o el bloque de instrucciones --la variable tabla se eliminara auitomaticamente. Go

SELECT * FROM @EstatusUsuarios;

Como podrn notar en el primer select nos regreso:

Y para el segundo select:

Esto es por que como lo comente anteriormente el tiempo de vida de la variable tabla termina al ejecutarse el bloque de instrucciones, en este caso termino con el GO. Cuando encontremos problemas de rendimiento, debemos de asegurarnos de probar todas las soluciones y alternativas, y no necesariamente asumir que una de las opciones (tablas Temporales) son menos deseables que otras (variables de tabla). Espero les sea de ayuda, se que esta un poco largo el Post, pero creo vale la pena leerlo.

Apyame te llevara 3 min:

votando

aqu: http://blogit.ms/TopBloggers.aspx en la categora SQL Server. Los pasos para votar estn aqu

Clic Aqu.

Etiquetas de Technorati: Tablas temporales,temporary tables,Local Tables,Global Tables,Variables de Tabla,SQL 2008

Utilizar Sentencias SQL

Esta pgina muestra un procedimiento almacenado muy sencillo que no tiene parmetros. Aunque la mayora de los procedimientos almacenados hacen cosas ms complejas que este ejemplo, sirve para ilustrar algunos puntos bsicos sobre ellos. Como paso prvio, la sintaxis para definir un procedimiento almacenado es diferente de un controlador de base de datos a otro. Por ejemplo, algunos utilizan begin . . . end u otras palabras clave para indicar el principio y final de la definicin de procedimiento. En algunos controladores, la siguiente sentencia SQL crea un procedimiento almacenado.

create procedure SHOW_SUPPLIERS as select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME from SUPPLIERS, COFFEES where SUPPLIERS.SUP_ID = COFFEES.SUP_ID order by SUP_NAME
El siguiente cdigo pone la sentencia SQL dentro de un string y lo asigna a la variablecreateProcedure, que utilizaremos ms adelante.

String createProcedure = "create procedure SHOW_SUPPLIERS " + "as " + "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " + "from SUPPLIERS, COFFEES " + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "order by SUP_NAME";
El siguiente fragmento de cdigo utiliza el objeto Connection, con para crear un objetoStatement, que es utilizado para enviar la sentencia SQL que crea el procedimiento almacenado en la base de datos.

Statement stmt = con.createStatement(); stmt.executeUpdate(createProcedure);

El procedimiento SHOW_SUPPLIERS ser compilado y almacenado en la base de datos como un objeto de la propia base y puede ser llamado, como se llamara a cualquier otro mtodo.

Llamar a un Procedimiento Almacenado desde JDBC

JDBC permite llamar a un procedimiento almacenado en la base de datos desde una aplicacin escrita en Java. El primer paso es crear un objeto CallableStatement. Al igual que con los objetos Statement y PreparedStatement, esto se hace con una conexin abierta,Connection. Un objeto CallableStatement contiene una llamada a un procedimiento almacenado; no contiene el propio procedimiento. La primera lnea del cdigo siguiente crea una llamada al procedimiento almacenado SHOW_SUPPLIERS utilizando la conexin con. La parte que est encerrada entre corchetes es la sintaxis de escape para los precedimientos almacenados. Cuando un controlador encuentra "{call SHOW_SUPPLIERS}", traducir esta sintaxis de escape al SQL nativo utilizado en la base de datos para llamar al procedimiento almacenado llamado SHOW_SUPPLIERS.

CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}"); ResultSet rs = cs.executeQuery();

La hoja de resultados de rs ser similar a esto.

SUP_NAME ---------------Acme, Inc. Acme, Inc. Superior Coffee Superior Coffee The High Ground

COF_NAME ----------------------Colombian Colombian_Decaf French_Roast French_Roast_Decaf Espresso

Observa que el mtodo utilizado para ejecutar cs es executeQuery porque cs llama a un procedimiento almacenado que contiene una peticin y esto produce una hoja de resultados. Si el procedimiento hubiera contenido una sentencia de actualziacin o una sentencia DDL, se hubiera utilizado el mtodo executeUpdate. Sin embargo, en algunos casos, cuando el procedimiento almacenado contiene ms

de una sentencia SQL producir ms de una hoja de resultados, o cuando contiene ms de una cuenta de actualizacinm o alguna combinacin de hojas de resultados y actualizaciones. en estos casos, donde existen mltiples resultados, se debera utilizar el mtodo execute para ejecutar CallableStatement. La clase CallableStatement es una subclase de PreparedStatement, por eso un objetoCallableStatement puede tomar parmetros de entrada como lo hara un objetoPreparedStatement. Adems, un objeto CallableStatement puede tomar parmetros de salida, o parmetros que son tanto de entrada como de salida. Los parmetros INOUT y el mtodo execute se utilizan raramente.

Ejecutar procedimiento almacenado desde Java (JDBC)

En Java invocar a un procedimiento almacenado que no devuelve valores es bastante sencillo hacerlo desde JDBC, funciona de la misma manera que con cualquier base de datos, solo es cuestin de cambiar la cadena de conexin y el driver, el resto va igual. En el ejemplo veremos con MySQL, pero como vengo diciendo cambien los 2 factores y podrn usarlo con cualquier otro motor de base de datos.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 package conexionDB; import java.sql.Connection; public class ConexionMySQL { public static Connection con_mysql;

public static Connection conectar(String pHost, String pUser, String pPassword, String Exception { try { String databaseURL = "jdbc:mysql://" + pHost + "/" + pDataBase; Class.forName("com.mysql.jdbc.Driver"); con_mysql = java.sql.DriverManager.getConnection(databaseURL, pUser, p //System.out.println("Conexion con MySQL Establecida.."); } catch (Exception e) { e.printStackTrace(); throw new Exception(e); } return con_mysql; } }

En la siguiente clase veremos como implementar la clase anterior para hacer la conexion, adems de invocar el procedimiento almacenado.
1 // imports necesarios 2 import conexionDB.ConexionMySQL; 3 import java.sql.CallableStatement;

4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

. . Connection connMY = null; try{ // creamos la conexion connMY = ConexionMySQL.conectar("192.168.1.100", "sa", "*******", "devtroce"); // establecemos que no sea autocommit, // asi controlamos la transaccion de manera manual connMY.setAutoCommit(false); /* instanciamos el objeto callable statement * que usaremos para invocar el SP * La cantidad de "?" determina la cantidad * parametros que recibe el procedimiento */ CallableStatement prcProcedimientoAlmacenado = connMY.prepareCall("{ call NuestroProce }"); // cargar parametros al SP prcProcedimientoAlmacenado.setInt("pParametro1", 1); prcProcedimientoAlmacenado.setString("pParametro2", "Devtroce.com"); prcProcedimientoAlmacenado.setInt("pParametro3", 49); // ejecutar el SP prcProcedimientoAlmacenado.execute(); // confirmar si se ejecuto sin errores connMY.commit(); } catch (Exception e) { // deshacer la ejecucion en caso de error connMY.rollback(); // informar por consola e.printStackTrace(); } finally { // cerrar la conexion connMY.close(); }

A pedido de Moise, edito el post y agrego la estructura de la tabla y un Stored Procedure que cumple para ejecutar con el cdigo aqu:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 CREATE TABLE devtroce( id int NOT NULL, campo2 varchar(50) NOT NULL, campo3 int NOT NULL ) ON PRIMARY CREATE PROCEDURE NuestroProcedimientoAlmacenado @id int, @campo2 varchar(50), @campo3 int AS BEGIN INSERT INTO devtroce VALUES ( @id, @campo2, @campo3); END

Publicarlo en Twitter

1 1 1 1 1 1 1 1 1

Publicarlo en Delicious Aadirlo a LinkedIn Publicarlo en Facebook