Skapa lagrade procedurer
Lagrade procedurer är ett av de mest kraftfulla verktygen i SQL Server för att kapsla in affärslogik och förbättra programprestanda. När du skapar lagrade procedurer skapar du återanvändbara kodblock som körs på servern, vilket minskar nätverkstrafiken och centraliserar dataåtkomstlogik.
Förstå lagrade procedurer
En lagrad procedur är en kompilerad samling T-SQL-instruktioner som SQL Server lagrar och kör som en enda enhet. Till skillnad från oplanerade frågor som du skickar till servern varje gång är lagrade procedurer förkompilerade och optimerade, vilket innebär att de körs snabbare vid efterföljande körningar.
Du använder lagrade procedurer för att kapsla in komplex affärslogik, framtvinga dataverifieringsregler och styra hur program interagerar med din databas. I stället för att till exempel tillåta direkt tabellåtkomst kan du skapa lagrade procedurer som validerar indata, tillämpar affärsregler och loggar innan du ändrar data.
Prestandafördelarna kommer från cachelagring av frågeplan. Med oplanerade frågor måste SQL Server parsa och optimera varje fråga varje gång. Med lagrade procedurer cachelagras körningsplanen efter den första körningen, vilket minskar kostnaderna för upprepade åtgärder.
Skapa grundläggande lagrade procedurer
När du skapar en lagrad procedur börjar du med instruktionen CREATE PROCEDURE följt av T-SQL-logiken. Du anger procedurnamnet med hjälp av en schemakvalificerad identifierare, vilket förbättrar tydligheten och prestandan.
CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC;
END
Instruktionen SET NOCOUNT ON förhindrar att meddelandet om antalet rader som påverkas skickas till klienten. Detta minskar nätverkstrafiken och förbättrar prestanda, särskilt när proceduren kör flera instruktioner.
När du skapar procedurer använder du nyckelorden BEGIN och END för att tydligt definiera procedurens brödtext. Detta gör koden mer läsbar och hjälper till att förhindra fel när du lägger till eller ändrar logik senare.
Arbeta med parametrar
Parametrar gör lagrade procedurer flexibla och återanvändbara. Du definierar indataparametrar för att acceptera värden från det anropande programmet och utdataparametrar för att returnera värden tillbaka till anroparen.
Indataparametrar använder @-symbolen följt av ett parameternamn och en datatyp. Du kan ange standardvärden för att göra parametrar valfria:
CREATE PROCEDURE dbo.GetCustomerOrdersByDate
@CustomerID int,
@StartDate datetime = NULL,
@EndDate datetime = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
OrderDate,
TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
AND (@StartDate IS NULL OR OrderDate >= @StartDate)
AND (@EndDate IS NULL OR OrderDate <= @EndDate)
ORDER BY OrderDate DESC;
END
Med utdataparametrar kan du returnera värden till det anropande programmet. Du definierar dem med hjälp av nyckelordet OUTPUT :
CREATE PROCEDURE dbo.CalculateOrderTotal
@OrderID int,
@TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @TotalAmount = SUM(Quantity * UnitPrice)
FROM dbo.OrderDetails
WHERE OrderID = @OrderID;
RETURN 0;
END
När du anropar en procedur med utdataparametrar måste du deklarera en variabel för att ta emot värdet och använda nyckelordet OUTPUT i -instruktionen EXECUTE .
Implementera felhantering
Robusta lagrade procedurer omfattar felhantering för att hantera oväntade förhållanden och upprätthålla dataintegritet. Du implementerar felhantering med hjälp av TRY...CATCH block som fungerar på samma sätt som undantagshantering på andra programmeringsspråk.
CREATE PROCEDURE dbo.InsertCustomerOrder
@CustomerID int,
@OrderDate datetime,
@TotalAmount decimal(10,2)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Validate customer exists
IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
BEGIN
RAISERROR('Customer does not exist.', 16, 1);
END
-- Insert order
INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
VALUES (@CustomerID, @OrderDate, @TotalAmount);
COMMIT TRANSACTION;
RETURN 0;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity int = ERROR_SEVERITY();
DECLARE @ErrorState int = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN -1;
END CATCH
END
Blocket TRY innehåller din huvudlogik, medan CATCH blocket hanterar eventuella fel som inträffar. Du kan använda systemfunktioner som ERROR_MESSAGE(), ERROR_SEVERITY()och ERROR_STATE() för att samla in felinformation och skicka dem till det anropande programmet.
Kontrollera @@TRANCOUNT alltid innan du återställer transaktioner i CATCH blocket. Detta förhindrar fel om transaktionen redan har slutförts eller aldrig startats.
Tillämpa bästa praxis
Genom att följa etablerade metodtips när du skapar lagrade procedurer ser du till att de är underhållsbara, säkra och högpresterande.
Använda schemakvalificerade namn
Använd schemakvalificerade namn för alla objekt. Detta eliminerar tvetydighet och förbättrar prestanda genom att undvika schemamatchningskostnader:
-- Good
SELECT * FROM dbo.Orders
-- Avoid
SELECT * FROM Orders
Implementera parametervalidation
Implementera parameterverifiering i början av proceduren. Misslyckas snabbt när indata är ogiltiga i stället för att bearbeta felaktiga data:
IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
RAISERROR('CustomerID must be a positive integer.', 16, 1);
RETURN -1;
END
Undvik SELECT *
Undvik SELECT * i produktionskod. Lista kolumnerna explicit för att förhindra problem när tabellstrukturer ändras och för att förbättra prestandan för frågor.
-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders
-- Avoid
SELECT * FROM dbo.Orders
Använda meningsfulla namn
Använd meningsfulla namn som beskriver vad proceduren gör. Inkludera ett verb som anger åtgärden (Get, Insert, Update, Delete, Calculate):
CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders
Undvik prefixet sp_
Använd inte prefixet sp_ för dina lagrade procedurer. SQL Server reserverar det här prefixet för systemprocedurer som lagras i master databasen. När du namnger en procedur med sp_söker master SQL Server först innan du kontrollerar den aktuella databasen, vilket lägger till onödiga omkostnader:
-- Good
CREATE PROCEDURE dbo.GetCustomerOrders
-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders
Genom att bygga vidare på dessa metoder kan du skapa lagrade procedurer som ditt team kan förstå, underhålla och lita på för att fungera tillförlitligt i produktionsmiljöer.