Microsoft SQL Server
Gespeicherte Prozeduren

Einführung

Syntax

• ERSTELLEN {PROCEDURE | PROC} [Schemaname.] Prozedurname
• [@parameter [Typ_Schema_Name.] Datentyp
• [VARYING] [= Standard] [OUT | AUSGABE | SCHREIBGESCHÜTZT ]
• , @parameter [Typ_Schema_Name.] Datentyp
• [VARYING] [= Standard] [OUT | AUSGABE | SCHREIBGESCHÜTZT ] ]
• [MIT {ENCRYPTION | RECOMPILE | EXECUTE AS-Klausel}]
• [ZUR ANTWORTUNG]
• WIE
• START
• [Deklarationsabschnitt]
• executable_section
• ENDE;

Erstellen und Ausführen einer einfachen gespeicherten Prozedur

Verwenden der Authors Tabelle in der Bibliotheksdatenbank

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

Sie können eine Prozedur mit einigen unterschiedlichen Syntaxen ausführen. Erstens können Sie EXECUTE oder EXEC

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

Darüber hinaus können Sie den Befehl EXEC weglassen. Sie müssen auch nicht angeben, welchen Parameter Sie übergeben, da Sie alle Parameter übergeben.

GetName NULL, 'Ernest Hemingway'

Wenn Sie die Eingabeparameter in einer anderen Reihenfolge angeben möchten als in der Prozedur deklariert, können Sie den Parameternamen angeben und Werte zuweisen. Zum Beispiel

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

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

Die normale Reihenfolge zum Ausführen dieser Prozedur besteht darin, zuerst den Wert für @ Param1 und dann für @ Param2 anzugeben. So wird es ungefähr so ​​aussehen

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

Es ist jedoch auch möglich, dass Sie Folgendes verwenden können

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

Dabei legen Sie zuerst den Wert für @ param2 und @ Param1 Sekunde fest. Das bedeutet, dass Sie nicht die gleiche Reihenfolge einhalten müssen, wie sie in der Prozedur angegeben ist. Sie können jedoch eine beliebige Reihenfolge haben, wie Sie möchten. Sie müssen jedoch angeben, für welchen Parameter Sie den Wert einstellen

Greifen Sie auf eine gespeicherte Prozedur aus einer beliebigen Datenbank zu

Außerdem können Sie eine Prozedur mit einem Präfix sp_ Diese Procuedres können wie alle gespeicherten Systemprozeduren aufgrund des Standardverhaltens von SQL Server ohne Angabe der Datenbank ausgeführt werden. Wenn Sie eine gespeicherte Prozedur ausführen, die mit "sp_" beginnt, sucht SQL Server zuerst nach der Prozedur in der Master-Datenbank. Wenn die Prozedur nicht im Master gefunden wird, wird in der aktiven Datenbank gesucht. Wenn Sie über eine gespeicherte Prozedur verfügen, auf die Sie aus allen Datenbanken zugreifen möchten, erstellen Sie sie in master und verwenden Sie einen Namen, der das Präfix "sp_" enthält.

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

GESPEICHERTES VERFAHREN mit OUT-Parametern

Gespeicherte Prozeduren können Werte mit dem Schlüsselwort OUTPUT in ihrer Parameterliste zurückgeben.

Erstellen einer gespeicherten Prozedur mit einem einzelnen Out-Parameter

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

Ausführen der gespeicherten Prozedur

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

Erstellen einer gespeicherten Prozedur mit mehreren Out-Parametern

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

Ausführen der gespeicherten Prozedur

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

Gespeicherte Prozedur mit If ... Else und Insert Ino Operation

Erstellen Sie eine Beispieltabelle Employee :

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

Erstellt eine gespeicherte Prozedur, die prüft, ob die in der gespeicherten Prozedur übergebenen Werte nicht null oder nicht leer sind, und führt eine Einfügeoperation in der Employee-Tabelle aus.

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

Führen Sie die gespeicherte Prozedur aus

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

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

Dynamisches SQL in gespeicherter Prozedur

Mit Dynamic SQL können wir zur Laufzeit SQL-Anweisungen generieren und ausführen. Dynamisches SQL ist erforderlich, wenn unsere SQL-Anweisungen einen Bezeichner enthalten, der sich zu verschiedenen Kompilierungszeiten ändern kann.

Einfaches Beispiel für dynamisches 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 der obigen SQL-Abfrage können wir sehen, dass wir die obige Abfrage verwenden @table_name, @col_name, and @col_value zur Laufzeit Werte in @table_name, @col_name, and @col_value . Die Abfrage wird zur Laufzeit generiert und ausgeführt. Dies ist eine Technik, bei der wir ganze Skripte als String in einer Variablen erstellen und ausführen können. Wir können komplexere Abfragen mit dynamischen SQL- und Verkettungskonzepten erstellen. Dieses Konzept ist sehr nützlich, wenn Sie ein Skript erstellen möchten, das unter verschiedenen Bedingungen verwendet werden kann.

Gespeicherte Prozedur ausführen

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

Tabelle, die ich verwendet habe

Ausgabe

Einfaches Looping

#systables uns zunächst einige Daten in eine temporäre Tabelle mit dem Namen #systables und eine #systables sodass wir jeweils einen Datensatz abfragen können

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

Als Nächstes deklarieren wir einige Variablen zur Steuerung der Schleife und speichern den Tabellennamen in diesem Beispiel

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

Jetzt können wir eine einfache Schleife verwenden. Wir @rn in der select Anweisung, dies könnte jedoch auch eine separate Anweisung für ex set @rn = @rn + 1 sein, je nach Ihren Anforderungen. Wir verwenden auch den Wert von @rn bevor er erhöht wird, um einen einzelnen Datensatz aus #systables . Zuletzt drucken wir den Tabellennamen.

while @rn <= @maxRn
    begin

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

        print @tablename
    end

Einfaches Looping

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