Contents
USE TSQL2012;
GO
SET NOCOUNT ON;
SELECT orderid, custid, shipperid, orderdate, requireddate, shippeddate
FROM Sales.Orders
WHERE custid = 37
AND orderdate >= '2007-04-01'
AND orderdate < '2007-07-01';
DECLARE
@custid AS INT,
@orderdatefrom AS DATETIME,
@orderdateto AS DATETIME;
SET @custid = 37;
SET @orderdatefrom = '2007-04-01';
SET @orderdateto = '2007-07-01';
WHERE custid = @custid
AND orderdate >= @orderdatefrom
AND orderdate < @orderdateto;
IF OBJECT_ID('Sales.GetCustomerOrders', 'P') IS NOT NULL
DROP PROC Sales.GetCustomerOrders;
CREATE PROC Sales.GetCustomerOrders
@orderdatefrom AS DATETIME = '19000101',
@orderdateto AS DATETIME = '99991231',
@numrows AS INT = 0 OUTPUT
AS
BEGIN
SET @numrows = @@ROWCOUNT;
RETURN;
END
DECLARE @rowsreturned AS INT;
EXEC Sales.GetCustomerOrders
@custid = 37,
@orderdatefrom = '20070401',
@orderdateto = '20070701',
@numrows = @rowsreturned OUTPUT;
SELECT @rowsreturned AS "Rows Returned";
FROM [Sales].[Orders]
EXEC sp_configure;
EXEC Sales.GetCustomerOrders 37, '20070401', '20070701';
EXEC Sales.GetCustomerOrders @custid = 37, @orderdatefrom = '20070401', @orderdateto = '20070701';
@custid = 37;
AS <rest of procedure>
@numrows = @rowsreturned;
SELECT @rowsreturned AS 'Rows Returned';
DECLARE @var1 AS INT, @var2 AS INT;
SET @var1 = 1;
SET @var2 = 2;
IF @var1 = @var2
PRINT 'The variables are equal';
ELSE
PRINT 'The variables are not equal';
SET @var2 = 1;
PRINT '@var1 does not equal @var2';
PRINT '@var1 equals @var2';
DECLARE @count AS INT = 1;
WHILE @count <= 10
PRINT CAST(@count AS NVARCHAR);
SET @count += 1;
END;
WHILE @count <= 100
IF @count = 10
BREAK;
IF @count = 5
SET @count += 2;
CONTINUE;
DECLARE @categoryid AS INT;
SET @categoryid = (SELECT MIN(categoryid) FROM Production.Categories);
WHILE @categoryid IS NOT NULL
PRINT CAST(@categoryid AS NVARCHAR);
SET @categoryid = (SELECT MIN(categoryid) FROM Production.Categories
WHERE categoryid > @categoryid);
DECLARE @categoryname AS NVARCHAR(15);
SET @categoryname = (SELECT MIN(categoryname) FROM Production.Categories);
WHILE @categoryname IS NOT NULL
PRINT @categoryname;
SET @categoryname = (SELECT MIN(categoryname) FROM Production.Categories
WHERE categoryname > @categoryname);
WAITFOR DELAY '00:00:20';
WAITFOR TIME '23:46:00';
PRINT 'First PRINT statement';
GOTO MyLabel;
PRINT 'Second PRINT statement';
MyLabel:
PRINT 'End';
IF OBJECT_ID('Sales.ListSampleResultsSets', 'P') IS NOT NULL
DROP PROC Sales.ListSampleResultsSets;
CREATE PROC Sales.ListSampleResultsSets
SELECT TOP (1) productid, productname, supplierid,
categoryid, unitprice, discontinued
FROM Production.Products;
SELECT TOP (1) orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails;
EXEC Sales.ListSampleResultsSets
--CREATE TRIGGER TriggerName
-- ON [dbo].[TableName]
-- FOR DELETE, INSERT, UPDATE
-- AS
-- BEGIN
-- SET NOCOUNT ON
IF OBJECT_ID('Sales.tr_SalesOrderDetailsDML', 'TR') IS NOT NULL
DROP TRIGGER Sales.tr_SalesOrderDetailsDML;
CREATE TRIGGER Sales.tr_SalesOrderDetailsDML
ON Sales.OrderDetails
AFTER DELETE, INSERT, UPDATE
IF @@ROWCOUNT = 0 RETURN; -- Must be 1st statement
IF @@ROWCOUNT = 0 RETURN;
SELECT COUNT(*) AS InsertedCount FROM Inserted;
SELECT COUNT(*) AS DeletedCount FROM Deleted;
IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL
DROP TRIGGER Production.tr_ProductionCategories_categoryname;
CREATE TRIGGER Production.tr_ProductionCategories_categoryname
ON Production.Categories
AFTER INSERT, UPDATE
IF EXISTS (SELECT COUNT(*)
FROM Inserted AS I
JOIN Production.Categories AS C
ON I.categoryname = C.categoryname
GROUP BY I.categoryname
HAVING COUNT(*) > 1 )
THROW 50000, 'Duplicate category names not allowed', 0;
INSERT INTO Production.Categories (categoryname,description)
VALUES ('TestCategory1', 'Test1 description v1');
UPDATE Production.Categories
SET categoryname = 'Beverages' WHERE categoryname = 'TestCategory1';
DELETE FROM Production.Categories WHERE categoryname = 'TestCategory1';
EXEC sp_configure 'nested triggers';
INSTEAD OF INSERT
INSERT Production.Categories (categoryname, description)
SELECT categoryname, description FROM Inserted;
IF UPDATE(qty)
PRINT 'Column qty affected';
UPDATE Sales.OrderDetails
SET qty = 99
WHERE orderid = 10249 AND productid = 16;
CREATE FUNCTION dbo.FunctionName
(
@param1 int,
@param2 int
)
RETURNS INT
RETURN @param1 + @param2
DROP FUNCTION Sales.fn_extension
CREATE FUNCTION Sales.fn_extension
@unitprice AS MONEY,
@qty AS INT
RETURNS MONEY
RETURN @unitprice * @qty
SELECT Orderid, unitprice, qty, Sales.fn_extension(unitprice, qty) AS extension
FROM Sales.OrderDetails
WHERE Sales.fn_extension(unitprice, qty) > 1000;
@param2 char(5)
RETURNS TABLE AS RETURN
SELECT @param1 AS c1,
@param2 AS c2
IF OBJECT_ID('Sales.fn_FilteredExtension', 'IF') IS NOT NULL
DROP FUNCTION Sales.fn_FilteredExtension;
CREATE FUNCTION Sales.fn_FilteredExtension
@lowqty AS SMALLINT,
@highqty AS SMALLINT
SELECT orderid, unitprice, qty
WHERE qty BETWEEN @lowqty AND @highqty
);
FROM Sales.fn_FilteredExtension (10,20);
<SELECT …>
RETURNS @returntable TABLE
c1 int,
c2 char(5)
INSERT @returntable
SELECT @param1, @param2
RETURN
IF OBJECT_ID('Sales.fn_FilteredExtension2', 'TF') IS NOT NULL
DROP FUNCTION Sales.fn_FilteredExtension2;
CREATE FUNCTION Sales.fn_FilteredExtension2
orderid INT,
unitprice MONEY,
qty SMALLINT
FROM Sales.fn_FilteredExtension2 (10,20);