Quiz Sebelum UTS

2. SP_Lihat_Pasien

CREATE PROCEDURE SP_Lihat_Pasien
@tahun char(2),
@bulan char(2)
AS
BEGIN

SELECT * from t_pasien
where substring(id_pasien,2,2) = @tahun and
substring(id_pasien,4,2) = @bulan
END
GO

3. SP_Lihat_Gologan Darah

CREATE PROCEDURE SP_Lihat_GolDarah
@gol_darah char(2)
AS
BEGIN

SELECT * from t_pasien
where gol_darah = @gol_darah
END
GO

4.SP_Tambah_Pasien


alter PROCEDURE SP_Tambah_Pasien
@nama varchar(30)

AS
BEGIN

declare @yy varchar(2)
declare @mm varchar(2)
declare @id_pasien varchar(10)
declare @no_akhir int
declare @no_baru int

set @yy = substring(Convert(varchar,(datepart(yy,getdate()))),3,2)


set @mm = right('0' + Convert(varchar,(datepart(mm,getdate()))),2)

select @no_akhir = Max(Convert(int,right(id_pasien,3))) from t_pasien
where substring(id_pasien,2,2) = @yy and
substring(id_pasien,4,2) = @mm
if(@no_akhir is null)
begin
set @no_baru = 1
end
else
begin
set @no_baru = @no_akhir + 1
end
print @no_baru
set @id_pasien = 'P'+ @yy + @mm + right('000' + Convert(varchar,@no_baru),3)

print @id_pasien

INSERT INTO [rumahsakit].[dbo].[t_pasien]
([id_pasien]
,[nama])
VALUES
(@id_pasien
,@nama)


END
GO


5. a

alter FUNCTION HitungUmur
(
@p1 datetime
)
RETURNS varchar(20)
AS
BEGIN

DECLARE @Result1 varchar(10)
DECLARE @Result2 varchar(10)
declare @result varchar(20)

set @Result1 = Convert(varchar,datediff(yy,@p1,getdate())-1)
set @Result2 = Convert(varchar,datediff(mm,@p1,getdate())-@Result1*12)

set @result = @Result1 + ' Tahun ' + @Result2 + ' Bulan'

return @result

END
GO

b.
create FUNCTION HitungDokter
(
@p1 varchar(50)
)
RETURNS int
AS
BEGIN

DECLARE @Result int

select @Result = count(id_dokter) from t_dokter where spesialisasi like '%' + @p1 + '%'

return @Result

END
GO