Por Alvaro
05/03/2009
Enviar esta pagina a un amigo.
Nos centraremos ahora en el código de los procedimientos almacenados.
Vamos a crearlos dentro de nuestra base de datos, bien mediante el explorador de servidores en
nuestro visual studio o bien desde sql management studio, pero recomendamos el visual studio por su simplicidad.
Entramos en la base de datos y dentro de la carpeta de los procedimientos almacenados, hacemos
clic con el botón derecho y damos a crear procedimiento almacenado nuevo.
En la ventana que nos sale pegamos el código que se encuentra a continuación, y le damos a grabar o salvar, tan simple como eso.
Aquí tenéis.
ALTER PROCEDURE dbo.UserRead
(
@ID
bigint
)
AS
SELECT ID, UserName, Password, Active,
Comments, Date_In, Date_Out
FROM tblUsers
WHERE Admin = 0 and ID
= @ID
RETURN
ALTER PROCEDURE dbo.UserModify
(
@ID
bigint,
@UserName
nvarchar(50),
@Password
nvarchar(50),
@Comments
varchar(MAX),
@Active
bit,
@Date_Out
datetime
)
AS
UPDATE tblUsers SET
UserName
= @UserName ,
Password
= @Password ,
Active
= @Active,
Comments
= @Comments,
Date_Out
= @Date_Out
WHERE ID = @ID
RETURN 0
ALTER PROCEDURE dbo.UserLogin
(
@UserName
nvarchar(50),
@Password
nvarchar(50)
)
AS
if (Select admin From tblUsers
Where UserName = @UserName and Password = @Password) = 1
BEGIN
Select *
From tblUsers Where UserName
= @UserName and Password = @Password
END
ELSE
BEGIN
Select * From tblUsers
Where UserName = @UserName and Password = @Password and
Active = 1 and Date_out > GETDATE()
END
RETURN
ALTER PROCEDURE dbo.UserDelete
(
@ID
bigint
)
AS
DECLARE @userexists
nvarchar(50)
SET @userexists
= (SELECT COUNT(*) FROM
tblUsers Where ID = @ID)
IF @userexists
= 0
BEGIN
RETURN 0
END
ELSE
BEGIN
DELETE FROM tblUsers WHERE ID
= @ID
SET @userexists = (SELECT
COUNT(*) FROM tblUsers Where ID = @ID)
IF @userexists = 0
BEGIN
RETURN 0
END
ELSE
BEGIN
RETURN -1
END
END
ALTER PROCEDURE dbo.UserAdd
(
@UserName
nvarchar(50),
@Password
nvarchar(50),
@Active
bit,
@Comments
varchar(MAX)
)
AS
DECLARE @userexists
nvarchar(50)
SET @userexists
= (SELECT COUNT(*) FROM
tblUsers Where UserName = @UserName)
IF @userexists
> 0
BEGIN
RETURN -1--User Exists
END
ELSE
BEGIN
INSERT INTO tblUsers
(
UserName,
Password,
Active,
Comments,
Date_In,
Date_Out,
admin
)
Values
(
@UserName,
@Password,
@Active,
@Comments,
GETDATE(),
GETDATE() + 365,
0
)
RETURN 0
END
ALTER PROCEDURE dbo.GetUsers
AS
SELECT tblUsers.ID,
tblUsers.UserName, Password, Active, Comments, Date_In,
Date_Out
FROM tblUsers
WHERE tblUsers.Admin=
0
RETURN
|