قطعه کد های کاربردی در sql

https://chrome.google.com/webstore/detail/majdfhpaihoncoakbjgbdhglocklcgno

ساخت فانکشن در sql چگونه است

به مثال زیر توجه کنید

create function tarakomMaadares(@nahie nvarchar) returns numeric

as

begin

declare @tarakom numeric

SELECT @tarakom = sum([totalStudents]/ [totlaClasses])

FROM madarestext231

WHERE [nahie] = @nahie

return @tarakom

end

create function tarakomMaadares5(@nahie nvarchar) returns numeric

as

begin

declare @tarakom numeric

SELECT @tarakom = sum([totalStudents]/ [totlaClasses])

FROM madarestext231

-- WHERE [codeMadrese] = @codeMadrese

WHERE not [totlaClasses] =0 and [nahie] = @nahie

return @tarakom

end

create function tarakomMaadares2(@codeMadrese numeric) returns numeric

as

begin

declare @tarakom numeric

SELECT @tarakom = [totalStudents]/ [totlaClasses]

FROM madarestext231

-- WHERE [codeMadrese] = @codeMadrese

return @tarakom

end

ave SUM:

SELECT student_code, AVG(lesson_rate) AS avg_lesson_rate , SUM(lesson_rate) AS sum_lesson_rate

FROM Unit

GROUP BY student_code

sen

SELECT student_code, DATEDIFF(year, student_birthday, GETDATE()) AS age

FROM Students

SELECT student_code, AVG(lesson_rate) AS avg_lesson_rate , SUM(lesson_rate) AS sum_lesson_rate 
FROM Unit
GROUP BY student_code

SELECT StudentId, AVG(NomreDars) as miangin,SUM(NomreDars) as jameNomre
FROM EntekhabVahed
GROUP BY StudentId

-- trigger 
CREATE TRIGGER update_total_vaheds
ON EntekhabVahed
AFTER UPDATE
AS
BEGIN
   DECLARE @StudentId INT;
   DECLARE @tedadKolVahedHa INT;
   SELECT @StudentId= StudentId FROM inserted
      PRINT @StudentId
   SELECT @tedadKolVahedHa= SUM(dbo.getTedadVahed(DarsId))
   FROM EntekhabVahed
   WHERE StudentId = @StudentId
   GROUP BY StudentId
      PRINT @tedadKolVahedHa

   UPDATE Students
   SET [tedadKolVahed] = @tedadKolVahedHa
   WHERE StudentId = @StudentId
END

-- create function 
create FUNCTION getTedadVahed2 (@darsId INT)
RETURNS INT
AS
BEGIN
	DECLARE @tedadVehd INT;
	SELECT @tedadVehd = TedadVahed
    FROM [dbo].[Dars]
    WHERE DarsId = @darsId

    RETURN @tedadVehd
END

غیره

create function tarakomMaadares(@nahie nvarchar) returns numeric
as
begin
declare @tarakom numeric
SELECT @tarakom = sum([totalStudents]/ [totlaClasses])
FROM madarestext231
WHERE [nahie] = @nahie
return @tarakom
end


create function tarakomMaadares5(@nahie nvarchar) returns numeric
as
begin
declare @tarakom numeric
SELECT @tarakom = sum([totalStudents]/ [totlaClasses])
FROM madarestext231
-- WHERE [codeMadrese] = @codeMadrese
WHERE not [totlaClasses] =0 and [nahie] = @nahie
return @tarakom
end

create function tarakomMaadares2(@codeMadrese numeric) returns numeric
as
begin
declare @tarakom numeric
SELECT @tarakom = [totalStudents]/ [totlaClasses]
FROM madarestext231
-- WHERE [codeMadrese] = @codeMadrese
return @tarakom
end