A0mcR45z

1SET ANSI_NULLS ON
2GO
3SET QUOTED_IDENTIFIER ON
4GO
5-- =============================================
6-- Author:		JLB
7-- Create date: 
8-- Description:	  Crud
9-- =============================================
10CREATE OR ALTER PROCEDURE InsertPotions 
11	-- Add the parameters for the stored procedure here
12    @nom_Potions  varchar(255),
13    @prix_potion_Potions numeric(10,3),
14    @id_categorie int
15AS
16BEGIN
17	SET NOCOUNT ON;
18    /* controle si l'id_catégorie existe */
19    IF NOT EXISTS( SELECT id_categorie FROM Catégories WHERE id_categorie = @id_categorie)
20        INSERT INTO [dbo].[Catégories]
21           ([id_categorie],[titre_categorie])
22        VALUES
23           (@id_categorie, '<nouvelle catégorie>')
24
25    /* insertion dans la table Potions */
26    INSERT INTO [dbo].[Potions]
27                ([nom_Potions]
28                ,[prix_potion_Potions]
29                ,[id_categorie])
30            VALUES
31                (@nom_Potions
32                ,@prix_potion_Potions
33                ,@id_categorie)
34END
35GO
36-- =============================================
37-- Author:		JLB
38-- Create date: 
39-- Description:	crUd
40-- =============================================
41CREATE OR ALTER PROCEDURE UpdatePotions 
42	-- Add the parameters for the stored procedure here
43    @id_Potions             int,
44    @nom_Potions            varchar(255),
45    @prix_potion_Potions    numeric(10,3),
46    @id_categorie           int
47AS
48BEGIN
49	SET NOCOUNT ON;
50    UPDATE [dbo].[Potions]
51       SET [nom_Potions]         = @nom_Potions 
52         , [prix_potion_Potions] = @prix_potion_Potions
53         , [id_categorie]       = @id_categorie
54     WHERE [id_potion_Potions] = @id_Potions ;
55END
56GO
57
58-- =============================================
59-- Author:		JLB
60-- Create date: 
61-- Description:	cruD
62-- =============================================
63CREATE OR ALTER PROCEDURE DeletePotions 
64	-- Add the parameters for the stored procedure here
65    @id_Potions             int
66AS
67BEGIN
68	SET NOCOUNT ON;
69    DELETE [dbo].[Potions] WHERE [id_potion_Potions] = @id_Potions ;
70END
71GO
72
73-- =============================================
74-- Author:		JLB
75-- Create date: 
76-- Description:	cRud
77-- =============================================
78CREATE OR ALTER PROCEDURE SelectAllPotions
79    @id_Potions int
80AS
81BEGIN
82	SET NOCOUNT ON;
83    SELECT * FROM [dbo].[Potions] ;
84END
85GO
86
87CREATE OR ALTER PROCEDURE SelectPotionsById
88    @id_Potions int
89AS
90BEGIN
91	SET NOCOUNT ON;
92    SELECT * FROM [dbo].[Potions] WHERE [id_potion_Potions] = @id_Potions ;
93END
94GO
95
96CREATE OR ALTER PROCEDURE SelectPotionsByName
97    @nom_Potions NVARCHAR(255)
98AS
99BEGIN
100	SET NOCOUNT ON;
101    SELECT * FROM [dbo].[Potions] 
102     WHERE [nom_Potions] = @nom_Potions ;
103END
104GO
105
106