AGENDA
SENTENCIAS TRANSACT SQL
STORED PROCEDURE
• AGENDA
INTRODUCCION A TRANSACT SQL
Validación TRANSACT
Sentencias TRANSACT
Validacion en TRANSACT - SP
Como saber si existe un objeto en la BD
Existen una serie de instrucciones para verificar si existen una serie de objetos en la BD:
Usando la función EXISTS de SQL y haciendo un SELECT a la tabla SYSOBJECTS, podemos saber si existe el objeto que estamos
buscando. El resultado de la funcion EXISTS nos devolvera un valor boleano:
USE NombreBaseDeDatos GO
IF EXISTS (SELECT * FROM sysobjects WHEREname=’sp_Procedimiento‘) BEGIN
print‘Existe’
END ELSE BEGIN
print‘No existe‘
END
Validacion en TRANSACT - SP
Sentencia SET NOCOUNT on
Evita que se devuelva el mensaje que muestra el recuento del número de filas afectadas por una instrucción o un procedimiento almacenado de Transact-SQL como parte del conjunto de resultados.
Si se establece SET NOCOUNT en ON, no se devuelve el recuento. Cuando SET NOCOUNT es OFF, sí se devuelve ese número.
La función @@ROWCOUNT se actualiza incluso cuando SET NOCOUNT es ON.
EJEMPLO:
USE AdventureWorks2012; GO SET NOCOUNT OFF; GO -- Display the count message.
SELECT TOP(5) LastName FROM Person.Person WHERE LastName LIKE 'A%';
GO
Validacion en TRANSACT - SP
SET NOCOUNT ON; GO
SELECT TOP(5) LastName FROM Person.Person WHERE LastName LIKE 'A%'; GO
Validacion en TRANSACT - SP
Sentencia @@ROWCOUNT
Devuelve el número de filas afectadas por la última instrucción.
Si el número de filas es mayor de dos mil millones, use ROWCOUNT_BIG
EJEMPLO:
USE AdventureWorks2008; GO
UPDATE HumanResources.Employee SET JobTitle = N'Executive'
WHERE NationalIDNumber = 123456789 IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
GO
Validacion en TRANSACT - SP
Sentencia set ANSI_NULLS ON
Especifica el comportamiento conforme a ISO de los operadores de
comparación Es igual a (=) y No es igual a (<>) cuando se usan con valores NULL.
En una versión futura de SQL Server, ANSI_NULLS siempre se establecerá en ON y cualquier aplicación que establezca de forma explícita la opción en OFF generará un error.
Cuando SET ANSI_NULLS se establece en ON, una instrucción SELECT que utilice WHERE column_name = NULL devuelve cero filas incluso si hay valores NULL encolumn_name. Una instrucción SELECT que utilice
WHERE column_name <> NULL devuelve cero filas incluso si hay valores no NULL en column_name
Validacion en TRANSACT - SP
Sentencia set ANSI_NULLS ON
Cuando SET ANSI_NULLS se establece en OFF, los operadores de comparación Es igual a (=) y No es igual a (<>) no siguen el estándar
ISO. Una instrucción SELECT que utilice
WHERE column_name = NULL devuelve las filas que tienen valores NULL en column_name. Una instrucción SELECT que utilice WHERE column_name <> NULL devuelve las filas que tienen valores no NULL en la columna. Además, una instrucción SELECT que utilice WHERE column_name <> XYZ_value devuelve todas las filas que no sonXYZ_value y que no son NULL.
EJEMPLO:
DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF';
IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_ NULLS = 'ON';
SELECT @ANSI_ NULLS AS ANSI_ NULLS;
Validacion en TRANSACT - SP
Sentencia set QUOTED_IDENTIFIER ON
Hace que SQL Server siga las reglas de ISO en cuanto a comillas delimitadoras de identificadores y cadenas literales se refiere.
Los identificadores delimitados con comillas dobles pueden ser palabras clave reservadas de Transact-SQL o pueden contener caracteres
normalmente no admitidos por las reglas sintácticas para identificadores de Transact-SQL.
EJEMPLO:
SET QUOTED_IDENTIFIER OFF GO
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL); GO SET QUOTED_IDENTIFIER ON; GO
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL); GO SELECT "identity","order" FROM "select" ORDER BY "order";
GO
DROP TABLE "SELECT"; GO
SET QUOTED_IDENTIFIER OFF; GO
INTRODUCCION A TRANSACT SQL
Creación de un stored procedure que cree una tabla, inserte datos de dos tablas
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON go
IF EXISTS (SELECT * FROM sysobjects WHEREname=’sp_inserta‘) BEGIN
drop procedure sp_inserta END
CREATE PROCEDURE [dbo].[sp_inserta]
AS BEGIN
SET NOCOUNT ON;
WHILE EXISTS ( select A.cedula, A.nombre, A.direccion, B.valor FROM clientes A INNER JOIN compras B
ON B.cedula = A.cedula)
INTRODUCCION A TRANSACT SQL
Creación de un stored procedure que cree una tabla, inserte datos de dos tablas
BEGIN
CREATE TABLE [dbo].[nuevo] ( [cedula] [int] NOT NULL,
[nombre] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL, [direccion] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL, [valor] [int] NULL,
CONSTRAINT [PK_nuevo] PRIMARY KEY CLUSTERED (
[cedula] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [bdejercicio].[dbo].[nuevo]
([cedula]
,[nombre]
,[direccion]
,[valor])
select A.cedula,A.nombre,A.direccion,B.valor
INTRODUCCION A TRANSACT SQL SENTENCIA SELECT
Recupera filas de la base de datos y habilita la selección de una o varias filas o columnas de una o varias tablas en SQL Server.
La sintaxis completa de la instrucción SELECT es compleja, aunque las cláusulas principales se pueden resumir del modo siguiente:
[ WITH <common_table_expression>]
SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
Los operadores UNION, EXCEPT e INTERSECT se pueden utilizar entre consultas para combinar o comparar resultados en un conjunto de
resultados.
INTRODUCCION A TRANSACT SQL SENTENCIA SELECT
Recupera filas de la base de datos y habilita la selección de una o varias filas o columnas de una o varias tablas en SQL Server.
La sintaxis completa de la instrucción SELECT es compleja, aunque las cláusulas principales se pueden resumir del modo siguiente:
[ WITH <common_table_expression>]
SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
Los operadores UNION, EXCEPT e INTERSECT se pueden utilizar entre consultas para combinar o comparar resultados en un conjunto de
resultados.
INTRODUCCION A TRANSACT SQL
SENTENCIA SELECT: ORDEN DE PROCESAMIENTO LOGICO
Los pasos siguientes muestran el orden de procesamiento lógico, u orden de enlaces, de una instrucción SELECT.
Este orden determina cuándo los objetos definidos en un paso están disponibles para las cláusulas en pasos posteriores.
1) FROM 2) ON 3) JOIN 4) WHERE 5) GROUP BY
6) WITH CUBE o WITH ROLLUP 7) HAVING
8) SELECT 9) DISTINCT 10) ORDER BY 11) TOP
INTRODUCCION A TRANSACT SQL SENTENCIA SELECT: EJEMPLOS
1.- USE AdventureWorks2008; GO
SELECT * FROM Production.Product ORDER BY Name ASC; GO
2.- USE AdventureWorks2008; GO
SELECT p.* FROM Production.Product AS p ORDER BY Name ASC; GO
3.- USE AdventureWorks2008; GO
SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product ORDER BY Name ASC; GO
4.- USE AdventureWorks2008; GO
SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product WHERE ProductLine = 'R' AND DaysToManufacture < 4
INTRODUCCION A TRANSACT SQL
SENTENCIA SELECT: USAR SELECT CON ENCABEZADOS DE COLUMNAS Y CALCULOS
1.- USE AdventureWorks2008; GO SELECT p.Name AS ProductName,
NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN
Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
ORDER BY ProductName DESC; GO
2.- USE AdventureWorks2008; GO
SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ', p.Name AS ProductName
FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID ORDER BY ProductName ASC; GO
INTRODUCCION A TRANSACT SQL
SENTENCIA SELECT: USAR DISTINCT CON SELECT
1.- USE AdventureWorks2008; GO SELECT DISTINCT JobTitle
FROM HumanResources.Employee ORDER BY JobTitle; GO
INTRODUCCION A TRANSACT SQL
SENTENCIA SELECT: CREAR TABLAS CON SELECT INTO
1.- USE tempdb; GO
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL DROP TABLE #Bicycles; GO SELECT * INTO #Bicycles
FROM AdventureWorks2008.Production.Product WHERE ProductNumber LIKE 'BK%'; GO
2.- USE AdventureWorks2008; GO
IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL DROP TABLE dbo.NewProducts; GO ALTER DATABASE AdventureWorks2008
SET RECOVERY BULK_LOGGED; GO SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25 AND ListPrice < $100; GO ALTER DATABASE AdventureWorks2008
SET RECOVERY FULL; GO
INTRODUCCION A TRANSACT SQL
SENTENCIA SELECT: USAR SUBCONSULTAS RELACIONADAS
1.- USE AdventureWorks2008; GO SELECT DISTINCT Name FROM Production.Product AS p
WHERE EXISTS (SELECT * FROM Production.ProductModel AS pm WHERE p.ProductModelID = pm.ProductModelID AND
pm.Name LIKE 'Long-Sleeve Logo Jersey%');
GO
EXISTE OTRA VARIANTE:
USE AdventureWorks2008; GO SELECT DISTINCT Name FROM Production.Product
WHERE ProductModelID IN (SELECT ProductModelID FROM Production.ProductModel WHERE Name LIKE 'Long-Sleeve Logo Jersey%');
GO
INTRODUCCION A TRANSACT SQL
SENTENCIA SELECT: USAR SUBCONSULTAS RELACIONADAS
2.- USE AdventureWorks2008; GO
SELECT DISTINCT p.LastName, p.FirstName FROM Person.Person AS p
JOIN HumanResources.Employee AS e ON e.BusinessEntityID = p.BusinessEntityID
WHERE 5000.00 IN (SELECT Bonus FROM Sales.SalesPerson AS sp WHERE e.BusinessEntityID = sp.BusinessEntityID);
GO
3.- USE AdventureWorks2008; GO
SELECT p1.ProductModelID FROM Production.Product AS p1 GROUP BY p1.ProductModelID
HAVING MAX(p1.ListPrice) >= ALL (SELECT AVG(p2.ListPrice)
FROM Production.Product AS p2
WHERE p1.ProductModelID = p2.ProductModelID);
GO
INTRODUCCION A TRANSACT SQL
SENTENCIA SELECT: USAR GROUP BY
1.- USE AdventureWorks2008; GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID ORDER BY SalesOrderID;
GO
2.- USE AdventureWorks2008; GO
SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS [Average Price], SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID ORDER BY ProductID;
GO
INTRODUCCION A TRANSACT SQL
SENTENCIA SELECT: USAR GROUP BY
3.- USE AdventureWorks2008; GO
SELECT ProductModelID, AVG(ListPrice) AS [Average List Price]
FROM Production.Product WHERE ListPrice > $1000 GROUP BY ProductModelID ORDER BY ProductModelID;
GO
4.- USE AdventureWorks2008; GO
SELECT ProductID, AVG(UnitPrice) AS [Average Price]
FROM Sales.SalesOrderDetail WHERE OrderQty > 10
GROUP BY ProductID ORDER BY AVG(UnitPrice);
GO
