===================================================================================================================================================================
DYNAMIC SQL
===================================================================================================================================================================
=> SQL COMMANDS BUILT AT RUNTIME ARE CALLED DYNAMIC SQL COMMANDS
=> DYNAMIC SQL IS USEFUL WHEN WE DONT KNOW TABLE NAMES AND COLUMN NAMES UNTIL RUN TIME.

=> SELECT * FROM EMP1 (STATIC QUERY)

=> DYNAMIC -- SQL

SELECT * FROM EMP


DECLARE @TNAME VARCHAR(20)
DECLARE @STMT VARCHAR(200)
SET @TNAME = 'DEPT'
SET @STMT = 'SELECT * FROM ' + @TNAME
EXEC (@STMT)



=> DYNAMIC SQL CAN BE EXECUTED BY USING
1. EXEC OR EXECUTE COMMAND
2. SP_EXECUTESQL PROCEDURE --SUPPORTS INPUT AND OUTPUT PARAMTERS


SELECT * INTO EMP11 FROM EMP

SELECT * FROM EMP11

=> CREATE A PROCEDURE TO DROP A PARTICULAR TABLE

CREATE PROC DROP_TABLE
@TNAME VARCHAR(20)
AS
EXEC ('DROP TABLE ' + @TNAME)


EXEC DROP_TABLE EMP11


EXECUTION -->  EXEC DROP_TABLE 'EMP11'
===================================================================================================================================================================
ALTER PROC EXE_QUERY
AS
DECLARE @TNAME VARCHAR(20)
SET @TNAME = 'EMP'
DECLARE @COL1  VARCHAR(20)
SET @COL1 = 'ENAME'
DECLARE @COL2  VARCHAR(20)
SET @COL2 = 'JOB'
DECLARE @STMT  VARCHAR(500)

SET @STMT = 'SELECT '+ @COL1 + ',' + @COL2 + ' FROM ' + @TNAME
PRINT @STMT

SP_EXECUTESQL EXE_QUERY

SELECT ENAME,JOB FROM EMP

Completion time: 2024-09-19T10:12:55.6752163+05:30

EXEC SP_NAME 

===================================================================================================================================================================

CREATE A PROCEDURE TO DISPLAY  DESIRED TWO COLUMNS  FROM A DESIRED TABLE
MEANS -- USER WILL SUPPLY THE COL NAMES AND TABLE NAME

ALTER PROC DYNAMIC_TABLE
 @TNAME VARCHAR(20),
 @COL1  VARCHAR(20),
 @COL2  VARCHAR(20),
 @COL3  VARCHAR(20)

AS
DECLARE @STMT  VARCHAR(500)
SET @STMT = 'SELECT '+ @COL1 + ',' + @COL2 + ',' + @COL3 +' FROM ' + @TNAME
PRINT @STMT
EXEC (@STMT)


EXEC DYNAMIC_TABLE 'EMP','EMPNO','ENAME','JOB'

SELECT * FROM EMP



===================================================================================================================================================================

CREATE A PROCEDURE TO DROP ALL THE TABLES FROM DATABASE 

CREATE PROC DROP_ALL_TABLES
AS
DECLARE C1 CURSOR FOR 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE @TNAME VARCHAR(20)
DECLARE @STMT VARCHAR(200)
OPEN C1
FETCH NEXT FROM C1 INTO @TNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STMT = 'DROP TABLE ' + @TNAME
EXEC (@STMT)
FETCH NEXT FROM C1 INTO @TNAME
END
CLOSE C1
DEALLOCATE C1

EXECUTE PROCEDURE ---> EXEC DROP_ALL_TABLES

===================================================================================================================================================================
ALTER PROC P_D
@TABLE VARCHAR(50)
AS
DECLARE @CMD VARCHAR(200)
SET @CMD = 'SELECT * FROM ' + @TABLE
EXEC(@CMD)

===> 
EXEC P_D 'EMP' 

===================================================================================================================================================================

SP_EXECUTESQL ----> IT IS A SYSTEM STORED PROCEDURE USED TO RUN ONE OR MULTIPLE SQL
STATEMENTS STORED INSIDE A STRING. IT SUPPORTS INPUT AND OUTPUT PARAMETERS

--EXEC SP_EXECUTESQL N'STATEMENT [{PARAMTERS DEFINITIONS}, {PARAMTER VALUES}]

SELECT * FROM EMP WHERE EMPNO = 110

EXEC SP_EXECUTESQL N'SELECT * FROM EMP WHERE EMPNO = 110'

===================================================================================================================================================================