CREATE TABLE DEPARTMENTS (
  ID INTEGER PRIMARY KEY,
  DEPARTMENT_NAME TEXT NOT NULL
);

-- insert
INSERT INTO DEPARTMENTS VALUES (1,'Sales TEAM');
INSERT INTO DEPARTMENTS VALUES (2,'Accounting');
INSERT INTO DEPARTMENTS VALUES (3,'MARKETING');
INSERT INTO DEPARTMENTS VALUES (4,'DATA SCIENCE');




CREATE TABLE EMPLOYEES (
  Id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  GENDER TEXT NOT NULL,
  DEPARTMENT_ID INTEGER,
  SALARY INTEGER,
  FOREIGN KEY (DEPARTMENT_ID) references DEPARTMENTS(ID)
);

-- insert
INSERT INTO EMPLOYEES VALUES (1, 'AHMED','MALE', 4,9000);
INSERT INTO EMPLOYEES VALUES (2, 'SARAH','FEMALE', 3,6000);
INSERT INTO EMPLOYEES VALUES (3, 'BODY','MALE', 1,6000);
INSERT INTO EMPLOYEES VALUES (4, 'SAMY','MALE', 2,6000);
INSERT INTO EMPLOYEES VALUES (5, 'OMI','FEMALE', 3,6000);
INSERT INTO EMPLOYEES VALUES (6, 'KHALTY','FEMALE', 3,6000);
INSERT INTO EMPLOYEES VALUES (7, 'NULL','FEMALE', 3,NULL);
INSERT INTO EMPLOYEES VALUES (8, 'SARAH','FEMALE', 2,5000);
INSERT INTO EMPLOYEES VALUES (9, 'AHMED','MALE', 2,6000);


-- ALTER TABLE EMPLOYEES
-- ADD CONSTRAINT EMPLOYEES_DEPARTMENTID_FK FOREIGN KEY(DEPARTMENTID) references DEPARTMENTS(DEPARTMENT_ID)

SELECT *
FROM EMPLOYEES;

SELECT *
FROM DEPARTMENTS;

create proc spnew
as
begin
select * from EMPLOYEES
end

create proc namne
@GENDER nvarchar(20),
@DEPARTMENT_ID int
as
begin
select name,GENDER,DEPARTMENT_ID
from EMPLOYEES
where GENDER = @GENDER and DEPARTMENT_ID = @DEPARTMENT_ID
end


namne 'MALE' , 1
-- lazem ara3y el tarteb bta3 el arguments 
-- aw aktek kol arg we el qema bta3toh 
-- we bkdah msh mohm el tarteb


namne @DEPARTMENT_ID = 1 , @GENDER = 'MALE'


-- 3shan a3raf el text ely maktob beh el stored proc 
-- hasta5dm el (system stored proc) >> (sp_helptext "name of my sp")

sp_helptext "namne"


-- ezay a3adel 3aleh

-- ast5dm (ALTER)

ALTER proc "proc_name"


-- hazfoh 

drop proc "proc_name"


-- law 3ayez t3ml increption leeh 

create proc "proc_name"
with encryption 



-- law 3ayez el sp traga3 VALUES

-- maaslan 3ayz yrga3ly 3add el records ele FEMALE maaslan

-- use the keyword (out or output)


create proc "proc_name"
@GENDER varchar(20),
@EMPLOYEESCount int out
as
begin
select @EMPLOYEESCount = count(ID)
from EMPLOYEES
where GENDER = @GENDER
end

declare "variable name" int

execute "proc_name" "MALE" , "variable name" out

-- 3ayez b2a atba3 2emet el variable dah

print "variable name"



-- system stored proc

1- sp_helptext 
2- sp_help
3- sp_dependes



-- mmkn badl ma arga3 output bst5dam out keyword

-- mmkn ast5dm (return)


create proc "proc_name"
as
begin
return (select count(ID) from EMPLOYEES)
end


-- 3shan a3mloh execute

1- ba3araf variable >> 

declare "totalempeyess" int


2- execute "totalempeyess" = "proc_name"

3- print "totalempeyess"


-- emta asta5dm return we emta out keyword


if i want return VALUES 

1- int >> mmkn easta5dm el etnen

2- text >> mynfa3sh return


el return >> btrga3 single VALUE




advantages