USING TYPE TABLE WITH CROSSAPPLY

----CREATE NORMAL TABLE

CREATE TABLE JOBALLOC(CID INT,PID INT,SIID INT)

---CREATE TYPE TABLE FOR INT VALUES(THEN WE PASS INTEGER VALUES)

CREATE TYPE TYPE_INT AS TABLE(ID INT)

---THIS TYPE DATE TYPE SHOULD BE "READONLY"

ALTER PROCEDURE USP_CROSSAPPLY(@CID TYPE_INT READONLY,@PID TYPE_INT READONLY,@SIID TYPE_INT READONLY)
AS
BEGIN
INSERT INTO JOBALLOC(CID,PID,SIID)
SELECT C.*,P.*,S.* FROM @CID C
CROSS APPLY @PID P
CROSS APPLY @SIID S
END

--- FIRST EXECUTE ABOVE PROCEDURE ,THEN INSERT VALUES INTO IS SHOWN BELOW

DECLARE @CID TYPE_INT
DECLARE @PID TYPE_INT
DECLARE @SIID TYPE_INT
INSERT INTO @CID VALUES(200),(201)
INSERT INTO @PID VALUES (300),(301)
INSERT INTO @SIID VALUES(400),(401)
EXEC USP_CROSSAPPLY @CID,@PID,@SIID

SELECT * FROM JOBALLOC


select * from sys.tables
---CROSSAPPLY:(CORTIGIAN JOIN)

SELECT COMM,SAL FROM EMP E CROSS APPLY DEPT D


SELECT COMM,SAL FROM EMP E , DEPT D

CREATE TABLE GG(ID INT)
CREATE TABLE JJ(ID INT)
INSERT INTO GG VALUES(1),(2),(3)
INSERT INTO JJ VALUES(4),(5),(6)

SELECT * FROM GG ,JJ

---------THEN WE HAVE ANOTHE TABLE

CREATE TABLE KK(ID INT)

INSERT INTO JJ VALUES(7),(8),(9)

----SELECT * FROM GG,JJ,KK

SELECT G.*,J.*,K.* FROM GG G CROSS APPLY JJ J CROSS APPLY KK K