· 8 years ago · Aug 18, 2018, 06:22 PM
1USE [master]
2GO
3/****** Object: Database [SHTORE_PROCEDURES] Script Date: 7/8/2018 3:58:47 p. m. ******/
4CREATE DATABASE [SHTORE_PROCEDURES]
5 CONTAINMENT = NONE
6 ON PRIMARY
7( NAME = N'SHTORE_PROCEDURES', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SHTORE_PROCEDURES.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
8 LOG ON
9( NAME = N'SHTORE_PROCEDURES_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SHTORE_PROCEDURES_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
10GO
11ALTER DATABASE [SHTORE_PROCEDURES] SET COMPATIBILITY_LEVEL = 110
12GO
13IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
14begin
15EXEC [SHTORE_PROCEDURES].[dbo].[sp_fulltext_database] @action = 'enable'
16end
17GO
18ALTER DATABASE [SHTORE_PROCEDURES] SET ANSI_NULL_DEFAULT OFF
19GO
20ALTER DATABASE [SHTORE_PROCEDURES] SET ANSI_NULLS OFF
21GO
22ALTER DATABASE [SHTORE_PROCEDURES] SET ANSI_PADDING OFF
23GO
24ALTER DATABASE [SHTORE_PROCEDURES] SET ANSI_WARNINGS OFF
25GO
26ALTER DATABASE [SHTORE_PROCEDURES] SET ARITHABORT OFF
27GO
28ALTER DATABASE [SHTORE_PROCEDURES] SET AUTO_CLOSE OFF
29GO
30ALTER DATABASE [SHTORE_PROCEDURES] SET AUTO_SHRINK OFF
31GO
32ALTER DATABASE [SHTORE_PROCEDURES] SET AUTO_UPDATE_STATISTICS ON
33GO
34ALTER DATABASE [SHTORE_PROCEDURES] SET CURSOR_CLOSE_ON_COMMIT OFF
35GO
36ALTER DATABASE [SHTORE_PROCEDURES] SET CURSOR_DEFAULT GLOBAL
37GO
38ALTER DATABASE [SHTORE_PROCEDURES] SET CONCAT_NULL_YIELDS_NULL OFF
39GO
40ALTER DATABASE [SHTORE_PROCEDURES] SET NUMERIC_ROUNDABORT OFF
41GO
42ALTER DATABASE [SHTORE_PROCEDURES] SET QUOTED_IDENTIFIER OFF
43GO
44ALTER DATABASE [SHTORE_PROCEDURES] SET RECURSIVE_TRIGGERS OFF
45GO
46ALTER DATABASE [SHTORE_PROCEDURES] SET DISABLE_BROKER
47GO
48ALTER DATABASE [SHTORE_PROCEDURES] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
49GO
50ALTER DATABASE [SHTORE_PROCEDURES] SET DATE_CORRELATION_OPTIMIZATION OFF
51GO
52ALTER DATABASE [SHTORE_PROCEDURES] SET TRUSTWORTHY OFF
53GO
54ALTER DATABASE [SHTORE_PROCEDURES] SET ALLOW_SNAPSHOT_ISOLATION OFF
55GO
56ALTER DATABASE [SHTORE_PROCEDURES] SET PARAMETERIZATION SIMPLE
57GO
58ALTER DATABASE [SHTORE_PROCEDURES] SET READ_COMMITTED_SNAPSHOT OFF
59GO
60ALTER DATABASE [SHTORE_PROCEDURES] SET HONOR_BROKER_PRIORITY OFF
61GO
62ALTER DATABASE [SHTORE_PROCEDURES] SET RECOVERY SIMPLE
63GO
64ALTER DATABASE [SHTORE_PROCEDURES] SET MULTI_USER
65GO
66ALTER DATABASE [SHTORE_PROCEDURES] SET PAGE_VERIFY CHECKSUM
67GO
68ALTER DATABASE [SHTORE_PROCEDURES] SET DB_CHAINING OFF
69GO
70ALTER DATABASE [SHTORE_PROCEDURES] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
71GO
72ALTER DATABASE [SHTORE_PROCEDURES] SET TARGET_RECOVERY_TIME = 0 SECONDS
73GO
74USE [SHTORE_PROCEDURES]
75GO
76/****** Object: Table [dbo].[PERFILES] Script Date: 7/8/2018 3:58:47 p. m. ******/
77SET ANSI_NULLS ON
78GO
79SET QUOTED_IDENTIFIER ON
80GO
81SET ANSI_PADDING ON
82GO
83CREATE TABLE [dbo].[PERFILES](
84 [id] [int] IDENTITY(1,1) NOT NULL,
85 [Perfil] [varchar](100) NOT NULL,
86 [Pin] [int] NOT NULL,
87 CONSTRAINT [PK_PERFILES] PRIMARY KEY CLUSTERED
88(
89 [id] ASC
90)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
91) ON [PRIMARY]
92
93GO
94SET ANSI_PADDING OFF
95GO
96/****** Object: Table [dbo].[USUARIOS] Script Date: 7/8/2018 3:58:47 p. m. ******/
97SET ANSI_NULLS ON
98GO
99SET QUOTED_IDENTIFIER ON
100GO
101SET ANSI_PADDING ON
102GO
103CREATE TABLE [dbo].[USUARIOS](
104 [Usuario] [varchar](100) NOT NULL,
105 [Mail] [varchar](100) NOT NULL,
106 [Contraseña] [varchar](100) NOT NULL,
107 [Nombre] [varchar](100) NOT NULL,
108 [Apellido] [varchar](100) NOT NULL,
109 [FkPerfil] [int] NOT NULL,
110 CONSTRAINT [PK_USUARIOS] PRIMARY KEY CLUSTERED
111(
112 [Usuario] ASC
113)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
114) ON [PRIMARY]
115
116GO
117SET ANSI_PADDING OFF
118GO
119ALTER TABLE [dbo].[PERFILES] WITH CHECK ADD CONSTRAINT [FK_PERFILES_PERFILES] FOREIGN KEY([id])
120REFERENCES [dbo].[PERFILES] ([id])
121GO
122ALTER TABLE [dbo].[PERFILES] CHECK CONSTRAINT [FK_PERFILES_PERFILES]
123GO
124ALTER TABLE [dbo].[USUARIOS] WITH CHECK ADD CONSTRAINT [FK_USUARIOS_PERFILES] FOREIGN KEY([FkPerfil])
125REFERENCES [dbo].[PERFILES] ([id])
126GO
127ALTER TABLE [dbo].[USUARIOS] CHECK CONSTRAINT [FK_USUARIOS_PERFILES]
128GO
129/****** Object: StoredProcedure [dbo].[sp_EliminarPerfil] Script Date: 7/8/2018 3:58:47 p. m. ******/
130SET ANSI_NULLS ON
131GO
132SET QUOTED_IDENTIFIER ON
133GO
134
135CREATE PROCEDURE [dbo].[sp_EliminarPerfil]
136
137 @pin int,
138 @perfil varchar(100)
139AS
140
141BEGIN
142 SET NOCOUNT ON;
143
144 IF EXISTS (SELECT * FROM PERFILES WHERE Perfil = @perfil AND Pin = @pin)
145 BEGIN
146 DELETE FROM PERFILES WHERE Perfil = @perfil AND Pin = @pin
147 END
148
149END
150
151GO
152/****** Object: StoredProcedure [dbo].[sp_EliminarUsuario] Script Date: 7/8/2018 3:58:47 p. m. ******/
153SET ANSI_NULLS ON
154GO
155SET QUOTED_IDENTIFIER ON
156GO
157
158CREATE PROCEDURE [dbo].[sp_EliminarUsuario]
159
160 @usuario varchar(100)
161AS
162
163BEGIN
164 SET NOCOUNT ON;
165
166 IF EXISTS (SELECT * FROM USUARIOS WHERE Usuario = @usuario)
167 BEGIN
168 DELETE FROM USUARIOS WHERE Usuario = @usuario
169 END
170
171END
172
173GO
174/****** Object: StoredProcedure [dbo].[sp_ListarUsuariosxPerfil] Script Date: 7/8/2018 3:58:47 p. m. ******/
175SET ANSI_NULLS ON
176GO
177SET QUOTED_IDENTIFIER ON
178GO
179
180CREATE PROCEDURE [dbo].[sp_ListarUsuariosxPerfil]
181
182 @perfil varchar(100)
183
184AS
185BEGIN
186
187 SET NOCOUNT ON;
188
189 SELECT * FROM USUARIOS
190 INNER JOIN PERFILES ON USUARIOS.FkPerfil = PERFILES.id
191 WHERE PERFILES.Perfil = @perfil
192END
193
194
195GO
196/****** Object: StoredProcedure [dbo].[sp_Login] Script Date: 7/8/2018 3:58:47 p. m. ******/
197SET ANSI_NULLS ON
198GO
199SET QUOTED_IDENTIFIER ON
200GO
201
202CREATE PROCEDURE [dbo].[sp_Login]
203
204 @usuario varchar(100),
205 @contraseña varchar(100)
206
207AS
208BEGIN
209
210 SET NOCOUNT ON;
211 DECLARE @contrahash varbinary(100)
212 SET @contrahash = CONVERT(varbinary(100), @contraseña)
213 SELECT HASHBYTES('SHA1', @contrahash)
214 IF EXISTS(SELECT * FROM USUARIOS WHERE Usuario = @usuario and Contraseña = @contrahash)
215 BEGIN
216 SELECT * FROM USUARIOS
217 END
218END
219GO
220/****** Object: StoredProcedure [dbo].[sp_ModificarUsuario] Script Date: 7/8/2018 3:58:47 p. m. ******/
221SET ANSI_NULLS ON
222GO
223SET QUOTED_IDENTIFIER ON
224GO
225CREATE PROCEDURE [dbo].[sp_ModificarUsuario]
226 @usuario varchar(100),
227 @mail varchar(100),
228 @contraseña varchar(100),
229 @nombre varchar(100),
230 @apellido varchar(100),
231 @perfil varchar(100)
232
233AS
234BEGIN
235
236 SET NOCOUNT ON;
237
238 IF (@mail is not NULL)
239 BEGIN
240 -- El usuario NO se puede cambiar.
241 UPDATE USUARIOS SET Mail = @mail
242 WHERE Usuario = @usuario
243 END
244
245 IF (@contraseña is not NULL)
246 BEGIN
247 -- El usuario NO se puede cambiar.
248 UPDATE USUARIOS SET Contraseña = @contraseña
249 WHERE Usuario = @usuario
250 END
251
252 IF (@nombre is not NULL)
253 BEGIN
254 -- El usuario NO se puede cambiar.
255 UPDATE USUARIOS SET Nombre = @nombre
256 WHERE Usuario = @usuario
257 END
258
259 IF (@apellido is not NULL)
260 BEGIN
261 -- El usuario NO se puede cambiar.
262 UPDATE USUARIOS SET Apellido = @apellido
263 WHERE Usuario = @usuario
264 END
265
266 IF (@perfil is not NULL)
267 BEGIN
268 -- El usuario NO se puede cambiar.
269 UPDATE USUARIOS SET @perfil = @perfil
270 WHERE Usuario = @usuario
271 END
272END
273
274GO
275/****** Object: StoredProcedure [dbo].[sp_RegistrarPerfil] Script Date: 7/8/2018 3:58:47 p. m. ******/
276SET ANSI_NULLS ON
277GO
278SET QUOTED_IDENTIFIER ON
279GO
280
281CREATE PROCEDURE [dbo].[sp_RegistrarPerfil]
282
283 @perfil varchar(100)
284AS
285
286BEGIN
287 SET NOCOUNT ON;
288
289 IF NOT EXISTS (SELECT * FROM PERFILES WHERE Perfil = @perfil)
290 BEGIN
291 INSERT INTO PERFILES (Perfil) VALUES (@perfil)
292 END
293
294END
295
296GO
297/****** Object: StoredProcedure [dbo].[sp_RegistrarUsuario] Script Date: 7/8/2018 3:58:47 p. m. ******/
298SET ANSI_NULLS ON
299GO
300SET QUOTED_IDENTIFIER ON
301GO
302CREATE PROCEDURE [dbo].[sp_RegistrarUsuario]
303 @usuario varchar(100),
304 @mail varchar(100),
305 @contraseña varchar(100),
306 @nombre varchar(100),
307 @apellido varchar(100),
308 @perfil varchar(100)
309
310AS
311BEGIN
312
313 SET NOCOUNT ON;
314
315 DECLARE @idperfil int
316 SELECT @idperfil = id FROM PERFILES WHERE Perfil = @perfil
317 DECLARE @contrahash varbinary(100)
318 SET @contrahash = CONVERT(varbinary(100), @contraseña)
319 SELECT HASHBYTES('SHA1', @contrahash)
320 INSERT INTO USUARIOS (Usuario, Mail, Contraseña, Nombre, Apellido, FkPerfil) VALUES (@usuario, @mail, @contrahash, @nombre, @apellido, @idperfil)
321END
322
323GO
324USE [master]
325GO
326ALTER DATABASE [SHTORE_PROCEDURES] SET READ_WRITE
327GO