Microsoft SQL Server
Opgeslagen procedures

Invoering

Syntaxis

• CREËER {PROCEDURE | PROC} [schema_name.] Procedure_name
• [@parameter [type_schema_name.] datatype
• [VARYING] [= standaard] [OUT | UITGANG | ALLEEN LEZEN ]
• , @parameter [type_schema_name.] datatype
• [VARYING] [= standaard] [OUT | UITGANG | ALLEEN LEZEN ] ]
• [MET {ENCRYPTIE | RECOMPILE | UITVOEREN ALS Clausule}]
• [VOOR REPLICATIE]
• NET ZO
• BEGINNEN
• [Declaration_section]
• executable_section
• EINDE;

Een basis opgeslagen procedure maken en uitvoeren

Gebruik van de tabel Authors in de bibliotheekdatabase

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

U kunt een procedure uitvoeren met een paar verschillende syntaxis. Ten eerste kunt u EXECUTE of EXEC

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

Bovendien kunt u de opdracht EXEC weglaten. Ook hoeft u niet op te geven welke parameter u doorgeeft, aangezien u alle parameters doorgeeft.

GetName NULL, 'Ernest Hemingway'

Als u de invoerparameters in een andere volgorde wilt opgeven dan hoe ze in de procedure worden gedeclareerd, kunt u de parameternaam opgeven en waarden toewijzen. Bijvoorbeeld

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

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

de normale volgorde om deze procedure uit te voeren, is eerst de waarde voor @ Param1 en vervolgens voor @ Param2 op te geven. Dus het ziet er ongeveer zo uit

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

Maar het is ook mogelijk dat u het volgende kunt gebruiken

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

hierin specificeert u eerst de waarde voor @ param2 en daarna @ Param1. Dit betekent dat u niet dezelfde bestelling hoeft te houden als die wordt aangegeven in de procedure, maar u kunt elke bestelling naar wens hebben. maar u moet opgeven voor welke parameter u de waarde instelt

Toegang tot opgeslagen procedure vanuit elke database

En u kunt ook een procedure maken met een voorvoegsel sp_ deze procuedres, net als alle in het systeem opgeslagen procedures, kunnen worden uitgevoerd zonder de database op te geven vanwege het standaardgedrag van SQL Server. Wanneer u een opgeslagen procedure uitvoert die begint met "sp_", zoekt SQL Server eerst de procedure in de hoofddatabase. Als de procedure niet in master wordt gevonden, wordt deze in de actieve database weergegeven. Als u een opgeslagen procedure hebt waartoe u toegang wilt krijgen vanuit al uw databases, maak deze dan aan in master en gebruik een naam met het voorvoegsel "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

OPGESLAGEN PROCEDURE met OUT-parameters

Opgeslagen procedures kunnen waarden retourneren met het sleutelwoord OUTPUT in de parameterlijst.

Een opgeslagen procedure maken met een enkele uit-parameter

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

De opgeslagen procedure uitvoeren

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

Een opgeslagen procedure maken met meerdere parameters

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

De opgeslagen procedure uitvoeren

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

Opgeslagen procedure met If ... Anders en in gebruik nemen

Voorbeeldtabel maken Employee :

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

Maakt een opgeslagen procedure waarmee wordt gecontroleerd of de waarden die zijn doorgegeven in de opgeslagen procedure niet nul of niet leeg zijn en de invoegbewerking uitvoeren in de tabel Werknemer.

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

Voer de opgeslagen procedure uit

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

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

Dynamische SQL in opgeslagen procedure

Met dynamische SQL kunnen we SQL-instructies genereren en uitvoeren tijdens runtime. Dynamische SQL is nodig wanneer onze SQL-instructies een ID bevatten die op verschillende compilatietijden kan veranderen.

Eenvoudig voorbeeld van dynamische 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

In de bovenstaande sql-query kunnen we zien dat we de bovenstaande query kunnen gebruiken door tijdens @table_name, @col_name, and @col_value waarden te definiëren in @table_name, @col_name, and @col_value . De query wordt tijdens runtime gegenereerd en uitgevoerd. Dit is een techniek waarbij we hele scripts als tekenreeks in een variabele kunnen maken en uitvoeren. We kunnen complexere query's maken met behulp van het dynamische SQL- en aaneenschakelingsconcept. Dit concept is zeer krachtig wanneer u een script wilt maken dat onder verschillende omstandigheden kan worden gebruikt.

Opgeslagen procedure uitvoeren

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

Tabel die ik heb gebruikt

uitgang

Eenvoudig doorlussen

#systables we eerst wat gegevens in een tijdelijke tabel genaamd #systables en een oplopend rijnummer toevoegen, zodat we één record per keer kunnen opvragen

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

Vervolgens verklaren we enkele variabelen om de looping te regelen en de tabelnaam in dit voorbeeld op te slaan

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

Nu kunnen we eenvoudig een lus gebruiken. We verhogen @rn in de select instructie, maar dit zou ook een aparte instructie kunnen zijn voor ex set @rn = @rn + 1 dit hangt af van uw vereisten. We gebruiken ook de waarde van @rn voordat deze wordt verhoogd om een enkel record uit #systables te selecteren. Ten slotte drukken we de tabelnaam af.

while @rn <= @maxRn
    begin

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

        print @tablename
    end

Eenvoudig doorlussen

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