Microsoft SQL Server
Lagrada förfaranden

Introduktion

Syntax

• SKAPA {FÖRFARANDE | PROC} [schema_name.] Procedurnamn
• [@parameter [typ_schema_name.] datatype
• [VARYING] [= standard] [OUT | UTGÅNG | LÄGENHET]
• , @parameter [typ_schema_name.] datatype
• [VARYING] [= standard] [OUT | UTGÅNG | LÄGENHET]]
• [MED {ENCRYPTION | REKOMPIL | EXECUTE AS Clause}]
• [FÖR FÖRSLAG]
• SOM
• BÖRJA
• [Declaration_section]
• executable_section
• SLUTET;

Skapa och genomföra en grundläggande lagrad procedur

Använda tabellen Authors i bibliotekets databas

CREATE PROCEDURE GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

Du kan utföra en procedur med några olika syntaxer. Först kan du använda EXECUTE eller EXEC

EXECUTE GetName @id = 1
EXEC Getname @name = 'Ernest Hemingway'

Dessutom kan du utelämna EXEC-kommandot. Du behöver inte heller ange vilken parameter du skickar in när du skickar in alla parametrar.

GetName NULL, 'Ernest Hemingway'

När du vill ange ingångsparametrarna i en annan ordning än hur de deklareras i proceduren kan du ange parameternamn och tilldela värden. Till exempel

 CREATE PROCEDURE dbo.sProcTemp 
 (
    @Param1 INT,
    @Param2 INT
)
AS
BEGIN

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

den normala ordningen för att utföra denna procedur är att ange värdet för @ Param1 först och sedan @ Param2 sekund. Så det kommer att se ut så här

  EXEC dbo.sProcTemp @Param1 = 0,@Param2=1

Men det är också möjligt att du kan använda följande

  EXEC dbo.sProcTemp @Param2 = 0,@Param1=1

i detta anger du värdet för @ param2 först och @ Param1 sekund. Vilket innebär att du inte behöver behålla samma ordning som den förklaras i proceduren men du kan ha vilken beställning du vill. men du måste ange vilken parameter du ställer in värdet

Få åtkomst till lagrad procedur från vilken databas som helst

Och du kan också skapa en procedur med ett prefix sp_ dessa upphandlade, liksom alla systemlagrade procedurer, kan köras utan att ange databasen på grund av standardbeteendet för SQL Server. När du kör en lagrad procedur som börjar med "sp_" letar SQL Server först efter proceduren i huvuddatabasen. Om proceduren inte finns i master ser den ut i den aktiva databasen. Om du har en lagrad procedur som du vill komma åt från alla dina databaser, skapar du den i master och använder ett namn som innehåller prefixet "sp_".

Use Master

CREATE PROCEDURE sp_GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

LAGRAD FÖRFARANDE med OUT-parametrar

Lagrade procedurer kan returnera värden med hjälp av OUTPUT nyckelordet i sin parameterlista.

Skapa en lagrad procedur med en enstaka parameter

CREATE PROCEDURE SprocWithOutParams
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam + ' must come out'   
    RETURN
END   
GO

Utför den lagrade proceduren

DECLARE @OutParam VARCHAR(30)    
EXECUTE SprocWithOutParams 'what goes in', @OutParam OUTPUT   
PRINT @OutParam 

Skapa en lagrad procedur med flera utparametrar

CREATE PROCEDURE SprocWithOutParams2
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT,
    @OutParam2 VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam +' must come out'   
    SELECT @OutParam2 = @InParam +' must come out'
    RETURN
END    
GO

Utför den lagrade proceduren

DECLARE @OutParam VARCHAR(30)    
DECLARE @OutParam2 VARCHAR(30)  
EXECUTE SprocWithOutParams2 'what goes in', @OutParam OUTPUT, @OutParam2 OUTPUT   
PRINT @OutParam 
PRINT @OutParam2

Lagrad procedur med If ... Annars och sätt in i drift

Skapa exempel tabell Employee :

CREATE TABLE Employee
(
    Id INT,
    EmpName VARCHAR(25),
    EmpGender VARCHAR(6),
    EmpDeptId INT
)

Skapar lagrad procedur som kontrollerar om värdena som sparats i lagrad procedur inte är noll eller icke tomma och utför insatsoperation i medarbetartabellen.

CREATE PROCEDURE spSetEmployeeDetails
(
    @ID int,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT
)
AS
BEGIN
    IF (
        (@ID IS NOT NULL AND LEN(@ID) !=0) 
        AND (@Name IS NOT NULL AND LEN(@Name) !=0)
        AND (@Gender IS NOT NULL AND LEN(@Gender) !=0)
        AND (@DeptId IS NOT NULL AND LEN(@DeptId) !=0)
    )
    BEGIN
        INSERT INTO Employee
        (
            Id,
            EmpName,
            EmpGender,
            EmpDeptId
        )
        VALUES
        (
            @ID,
            @Name,
            @Gender,
            @DeptId
        )
    END
ELSE
    PRINT 'Incorrect Parameters'
END
GO

Utför den lagrade proceduren

DECLARE @ID INT,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT

EXECUTE spSetEmployeeDetails
    @ID = 1,
    @Name = 'Subin Nepal',
    @Gender = 'Male',
    @DeptId = 182666 

Dynamisk SQL i lagrad procedur

Dynamisk SQL gör det möjligt för oss att generera och köra SQL-satser vid körning. Dynamisk SQL behövs när våra SQL-uttalanden innehåller identifierare som kan ändras vid olika sammanställningstider.

Enkelt exempel på dynamisk SQL:

CREATE PROC sp_dynamicSQL
@table_name      NVARCHAR(20),
@col_name        NVARCHAR(20), 
@col_value       NVARCHAR(20) 
AS
BEGIN
DECLARE  @Query  NVARCHAR(max)
SET      @Query = 'SELECT * FROM ' + @table_name
SET      @Query = @Query + ' WHERE ' + @col_name + ' = ' + ''''+@col_value+''''
EXEC     (@Query)
END

I ovanstående sqlfråga kan vi se att vi kan använda frågan ovan genom att definiera värden i @table_name, @col_name, and @col_value vid körning. Frågan genereras vid körning och körs. Detta är en teknik där vi kan skapa hela skript som sträng i en variabel och köra det. Vi kan skapa mer komplexa frågor med hjälp av dynamiskt SQL- och sammankopplingskoncept. Det här konceptet är mycket kraftfullt när du vill skapa ett skript som kan användas under flera förhållanden.

Utför lagrad procedur

DECLARE @table_name     NVARCHAR(20) = 'ITCompanyInNepal',
        @col_name       NVARCHAR(20) = 'Headquarter',
        @col_value      NVARCHAR(20) = 'USA'
    
EXEC    sp_dynamicSQL   @table_name,    
                        @col_name,
                        @col_value

Tabell jag har använt

Produktion

Enkel loopning

Låt #systables först få lite data i en temptabell med namnet #systables och #systables ett ökande radnummer så att vi kan fråga en post åt gången

select
    o.name,
    row_number() over (order by o.name) as rn
into
    #systables
from
    sys.objects as o
where
    o.type = 'S'

Därefter förklarar vi några variabler för att kontrollera loopingen och lagra tabellnamnet i det här exemplet

declare
    @rn int = 1,
    @maxRn int = (
                    select
                        max(rn)
                    from
                        #systables as s
                    )
declare    @tablename sys name

Nu kan vi slinga med ett enkelt tag. Vi ökar @rn i det select uttalandet men det kan också ha varit ett separat uttalande för ex set @rn = @rn + 1 det kommer att bero på dina krav. Vi använder också värdet på @rn innan det ökas för att välja en enda post från #systables . Slutligen skriver vi ut tabellnamnet.

while @rn <= @maxRn
    begin

        select
            @tablename = name,
            @rn = @rn + 1
        from
            #systables as s
        where
            s.rn = @rn

        print @tablename
    end

Enkel loopning

CREATE PROCEDURE SprocWithSimpleLoop
(
    @SayThis VARCHAR(30),
    @ThisManyTimes INT
)
AS
BEGIN
    WHILE @ThisManyTimes > 0
    BEGIN
        PRINT @SayThis;
        SET @ThisManyTimes = @ThisManyTimes - 1;
    END
    
    RETURN;
END    
GO