🍅

SQL Server の高度な使い方

に公開
SQL Server
tech

再帰

WITH TEMP AS (
	SELECT ID,PARENT_ID FROM [TABLE] A
	WHERE A.ID = ''
UNION ALL 
	SELECT ID,PARENT_ID FROM [TABLE] B
	JOIN TEMP C ON C.ID = B.PARENT_ID
)

SELECT * FROM TEMP;

Idは自動増分かどうかを調べる

  • 以下のソースを実行し判断ができます。1の場合idは自動増加です、0の場合idは自動増加じゃないです。
SELECT COLUMNPROPERTY(OBJECT_ID([TABLE_NAME],'ID','IsIdentity')) as Isidentity

ストアドプロシージャの作成と呼び出す

CREATE PROCEDURE GetEmployeeById
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;


EXEC GetEmployeeById @EmployeeID = 5;

関数の作成と呼び出す

CREATE FUNCTION dbo.GetEmployeesByDepartment
(
    @DepartmentID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);

SELECT * FROM dbo.GetEmployeesByDepartment(2);

動的SQL

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'SELECT * FROM Orders WHERE CustomerID = @CustID AND OrderDate >= @StartDate'

EXEC sp_executesql 
    @sql, 
    N'@CustID NVARCHAR(10), @StartDate DATE',
    @CustID = 'ALFKI',
    @StartDate = '2024-01-01'

Discussion