Stored Procedure para CRUD

Hemos empezado hacer algunos Stored Procedure que funcionan para conectar y hacer solicitudes simples en la base de datos como el login y la inscripción de los corredores a los campeonatos:
Tiempo de trabajo: 1:00 hora

Stored Procedure para Login:

create procedure [dbo].[SP_LoginRunner]
@Email varchar(50)
as
Begin
set nocount off
select R.code from Runners R where R.Email=@Email
end

GO

Stored Procedure para Inscripcion:

CREATE procedure [dbo].[SP_enrollinChallenge]
 @Email varchar(50),
 @challenge varchar(50)
 as
 begin
declare @FK_Challenges int;
declare @FK_Runner int;
declare @MaxNumber int;
declare @MaxID int;

select DISTINCT @FK_Runner=R.ID from Runners R where R.Email=@Email
select DISTINCT @FK_Challenges=C.ID from Challenge C where C.Name=@challenge
select @MaxNumber=max(CONVERT(INT, RxC.Number))+1 from RunnersXChallenge RxC where RxC.FK_Challenge=@FK_Challenges
select @MaxID=MAX(RxC.ID)+1 from RunnersXChallenge RxC
INSERT INTO dbo.RunnersXChallenge(ID,Number,AcumTime,AcumPoints,AcumPunishPts,FK_Challenge,FK_Runners,FK_Status,AcumPointsAuthorized,AcumPunishPtsAuthorized)
VALUES (@MaxID,@MaxNumber,default,0,0, @FK_Challenges, @FK_Runner,0,0,0)
end

GO

También se hicieron algunas funciones para encapsular lógica y mostrar un código mas simple:

Función para transformar segundos en tiempo:

CREATE FUNCTION [dbo].[FN_Seconds2Time]

(

-- Add the parameters for the function here

@Seconds BIGINT

)

RETURNS time(7)

AS

BEGIN

-- Declare the return variable here

DECLARE @RT time(7)

SELECT @RT = CAST(@Seconds/3600 AS VARCHAR(1000))

      + RIGHT(try_CONVERT(CHAR(8),DATEADD(ss,@Seconds,0),108),6)

RETURN @RT

END

GO

Función para sumar todos los tiempos de un corredor:

CREATE FUNCTION [dbo].[FN_SumRunnerTimeByRace]

(

-- Add the parameters for the function here

@Runner INT

)

RETURNS TIME(7)

AS

BEGIN

-- Declare the return variable here

DECLARE @AcumTimeSecs BIGINT

DECLARE @AcumTime TIME(7)

DECLARE @TimeByRunner TABLE(ET_Time datetimeoffset(7),ET_Time_Seconds BIGINT)

INSERT INTO @TimeByRunner(ET_Time,ET_Time_Seconds)

SELECT P.ET_Time, dbo.FN_Time2Seconds(p.ET_Time)

FROM Positions P

WHERE P.FK_Runners = @Runner

-- Add the T-SQL statements to compute the return value here

SELECT @AcumTimeSecs = (SELECT SUM(ET_Time_Seconds) FROM @TimeByRunner)

SELECT @AcumTime = dbo.FN_Seconds2Time(@AcumTimeSecs);

-- Return the result of the function

RETURN @AcumTime

END

GO