Benutzer:MovGP0/SQL/Programming
aus Wikipedia, der freien Enzyklopädie
< Benutzer:MovGP0 | SQL
Programming
DECLARE: Variables
DECLARE @AddressLine1 nvarchar(60) = 'Heldenplatz';
SELECT AddressId, AddressLine1
FROM Person.Address
WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%';
equal to:
DECLARE @AddressLine1 nvarchar(60);
SET @AddressLine1 = 'Heldenplatz';
SELECT AddressId, AddressLine1
FROM Person.Address
WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%';
Retrieve a Value into a Variable
DECLARE @AddressLine1 nvarchar(60);
DECLARE @AddressLine2 nvarchar(60);
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 66;
SELECT @AddressLine1 AS 'Address1', @AddressLine2 AS 'Address2';
@@ROWCOUNT: Determine how many Rows are affected
DECLARE @AddressLine1 nvarchar(60) = '59. Baker Street';
DECLARE @AddressLine2 nvarchar(60) = 'Reception Desk';
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 66;
IF @@ROWCOUNT = 1
SELECT @AddressLine1 AS 'Address1', @AddressLine2 AS 'Address2'
ELSE
SELECT 'Either no rows or to many rows found';
IF/THEN/ELSE
DECLARE @QuerySelector int = 3;
IF @QuerySelector = 1 BEGIN
SELECT TOP 3 ProductId, Name, COlor
FROM Production.Product
WHERE Color = 'Silver'
ORDER BY Name
END
ELSE BEGIN
SELECT TOP 3 ProductId, Name, Color
FROM Production.Product
WHERE Color = 'Black'
ORDER BY Name
END;
CASE
- CASE
SELECT DepartmentID AS DeptID, Name, GroupName,
CASE GroupName -- map group name column
WHEN 'Research and Development' THEN 'Room A'
WHEN 'Sales and Marketing' THEN 'Room B'
WHEN 'Manufacturing' THEN 'Room C'
ELSE 'ROOM D'
END AS ConferenceRoom -- into new ConferenceRoom column
FROM HumanResources.Department
- Searched CASE
SELECT DepartmentID AS DeptID, Name
CASE
WHEN Name = 'Research and Development' THEN 'Room A'
WHEN (Name = 'Sales and Marketing' OR DepartmentID = 10) THEN 'Room B'
WHEN Name LIKE '%T' THEN 'Room C'
ELSE 'ROOM D'
END AS ConferenceRoom -- into new ConferenceRoom column
FROM HumanResources.Department
WHILE
-- Declare variables
DECLARE @AWTables TABLE (SchemaTable varchar(100));
DECLARE @TableName varchar(100);
-- Insert table names into the table variable
INSERT @AWTables (SchemaTable)
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME;
-- Report on each table using sp_spaceused
WHILE (SELECT COUNT(*) FROM @AWTables) > 0 BEGIN
-- select first entry from the AWTables list
SELECT TOP 1 @TableName = SchemaTable
FROM @AWTables
ORDER BY SchemaTable;
-- execute stored procedure to determine table size
EXEC sp_spaceused @TableName;
-- remove entry from the AWTables list
DELETE @AWTables
WHERE SchemaTable = @TableName;
END;
