Microsoft SQL Server
Procédures stockées

Introduction

Syntaxe

• CREATE {PROCEDURE | PROC} [nom_schéma.] Nom_procédure
• [@parameter [type_schema_name.] type de données
• [VARYING] [= par défaut] [OUT | SORTIE | LECTURE SEULEMENT ]
• , @parameter [type_schema_name.] type de données
• [VARYING] [= par défaut] [OUT | SORTIE | LECTURE SEULEMENT ] ]
• [AVEC {CRYPTAGE | RECOMPILE | EXECUTE AS Clause}]
• [POUR RÉPLICATION]
• COMME
• COMMENCER
• [déclaration_section]
• exécutable_section
• FIN;

Créer et exécuter une procédure stockée de base

Utilisation de la table des Authors dans la base de données de la bibliothèque

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

Vous pouvez exécuter une procédure avec plusieurs syntaxes différentes. Tout d'abord, vous pouvez utiliser EXECUTE ou EXEC

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

De plus, vous pouvez omettre la commande EXEC. De plus, vous n'avez pas besoin de spécifier le paramètre que vous transmettez, lorsque vous transmettez tous les paramètres.

GetName NULL, 'Ernest Hemingway'

Lorsque vous souhaitez spécifier les paramètres d'entrée dans un ordre différent de celui dans lequel ils ont été déclarés dans la procédure, vous pouvez spécifier le nom du paramètre et affecter des valeurs. Par exemple

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

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

l'ordre normal d'exécution de cette procédure est de spécifier la valeur pour @ Param1 d'abord, puis @ Param2 seconde. Donc ça va ressembler à ça

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

Mais il est également possible que vous puissiez utiliser les éléments suivants

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

dans ce cas, vous spécifiez la valeur pour @ param2 en premier et @ Param1 seconde. Ce qui signifie que vous n'avez pas à conserver le même ordre que celui déclaré dans la procédure, mais vous pouvez avoir n'importe quelle commande comme vous le souhaitez. mais vous devrez spécifier à quel paramètre vous définissez la valeur

Accéder à la procédure stockée à partir de n'importe quelle base de données

Et vous pouvez également créer une procédure avec un préfixe sp_ ces procédures, comme toutes les procédures stockées du système, peuvent être exécutées sans spécifier la base de données en raison du comportement par défaut de SQL Server. Lorsque vous exécutez une procédure stockée commençant par "sp_", SQL Server recherche d'abord la procédure dans la base de données master. Si la procédure est introuvable dans master, elle recherche dans la base de données active. Si vous avez une procédure stockée à laquelle vous souhaitez accéder depuis toutes vos bases de données, créez-la dans master et utilisez un nom incluant le préfixe "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

PROCÉDURE STOCKÉE avec paramètres OUT

Les procédures stockées peuvent renvoyer des valeurs à l'aide du mot clé OUTPUT dans sa liste de paramètres.

Création d'une procédure stockée avec un seul paramètre de sortie

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

Exécuter la procédure stockée

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

Création d'une procédure stockée avec plusieurs paramètres sortants

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

Exécuter la procédure stockée

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

Procédure stockée avec If ... Else et Insert In operation

Créer une table exemple Employee :

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

Crée une procédure stockée qui vérifie si les valeurs transmises dans la procédure stockée ne sont pas nulles ou non et effectuent une opération d'insertion dans la table Employee.

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

Exécuter la procédure stockée

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

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

SQL dynamique dans la procédure stockée

SQL dynamique nous permet de générer et d'exécuter des instructions SQL au moment de l'exécution. Le SQL dynamique est nécessaire lorsque nos instructions SQL contiennent un identifiant susceptible de changer à différents moments de la compilation.

Exemple simple de SQL dynamique:

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

Dans la requête SQL ci-dessus, nous pouvons voir que nous pouvons utiliser la requête ci-dessus en définissant des valeurs dans @table_name, @col_name, and @col_value au moment de l'exécution. La requête est générée à l'exécution et exécutée. C'est une technique dans laquelle nous pouvons créer des scripts entiers sous forme de chaîne dans une variable et l'exécuter. Nous pouvons créer des requêtes plus complexes en utilisant le concept SQL dynamique et le concept de concaténation. Ce concept est très puissant lorsque vous souhaitez créer un script pouvant être utilisé dans plusieurs conditions.

Exécution de la procédure stockée

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

Tableau que j'ai utilisé

Sortie

Boucle simple

Premièrement, #systables des données dans une table temporaire nommée #systables et un numéro de ligne incrémenté afin de pouvoir interroger un enregistrement à la fois

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

Ensuite, nous déclarons des variables pour contrôler la boucle et stocker le nom de la table dans cet exemple

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

Maintenant, nous pouvons faire une boucle en utilisant un simple moment. Nous incrémentons @rn dans l'instruction select , mais cela pourrait aussi être une instruction séparée pour ex set @rn = @rn + 1 cela dépendra de vos besoins. Nous utilisons également la valeur de @rn avant de l'incrémenter pour sélectionner un seul enregistrement de #systables . Enfin, nous imprimons le nom de la table.

while @rn <= @maxRn
    begin

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

        print @tablename
    end

Boucle simple

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