DYNAMIC SQL

---DYNAMIC SQL
----MORE NO OF OPTINANL PARAMETERS WE HAVE THEN WE USE DYNAMIC SQL
----WE ARE PASSING ONE VALUE WE GET TOTAL ROW RESULT

CREATE TABLE T12(ID INT,NAME VARCHAR(20))
INSERT INTO T12 VALUES(1,'BABJI')
INSERT INTO T12 VALUES(2,'BHAVANI')
INSERT INTO T12 VALUES(3,'NAVEEN')
INSERT INTO T12 VALUES(4,'VINOD')
INSERT INTO T12 VALUES(5,'SEENU')
INSERT INTO T12 VALUES(6,'DARMA')
SELECT * FROM T12

ALTER PROC USP_DYNAMIC(@ID INT=NULL,@NAME VARCHAR(20)=NULL)
AS
BEGIN
DECLARE @SQL NVARCHAR(1000)='SELECT * FROM T12 WHERE 1=1'
IF(@ID IS NOT NULL)
BEGIN
SET @SQL=@SQL + 'AND ID=' + CAST(@ID AS VARCHAR(4))
END
IF(@NAME IS NOT NULL)
BEGIN
SET @SQL=@SQL + 'AND NAME=''' +@NAME +''''
END
EXEC(@SQL)
END
------EXECUTION

EXEC USP_DYNAMIC 1
EXECUTE USP_DYNAMIC @NAME='BHAVANI'

--ABOVE CONCADINATION:

SELECT * FROM T12 WHERE 1=1  AND ID=1
select * from T12 where 1=1 and NAME='BABJI'
 
WRITE A DYNAMIC SQL DEPTNO IS MANDATARY AND REMANING  COLUMNS ARE OPTIONAL:

CREATE PROC USP_DYNAMIC(@DEPTNO INT,@ENAME VARCHAR(30)=NULL,
                        @EMPNO INT=NULL,@JOB VARCHAR(30)=NULL)

AS
BEGIN
DECLARE @QRY NVARCHAR(MAX)=''
SET @QRY='SELECT * FROM EMP WHERE DEPTNO='+CAST(@DEPTNO AS VARCHAR(10))


IF @ENAME IS NOT NULL
BEGIN
    SET @QRY=@QRY+ 'AND ENAME='''+@ENAME+''''
   
END
IF @EMPNO IS NOT NULL
BEGIN
    SET @QRY=@QRY+'AND EMPNO='+CAST(@EMPNO AS VARCHAR(30))
END


IF @JOB IS NOT NULL
BEGIN
    SET @QRY=@QRY+'AND JOB='''+@JOB+''''
END
EXEC(@QRY)
END

EXEC USP_DYNAMIC 10,@JOB='PRESIDEN

Building Dynamic SQL In a Stored Procedure:

• A dynamic SQL in a stored procedure is a single Transact-SQL statement or a set of statements stored in a variable and executed using a SQL command. There may be several methods of implementing this in SQL Server. This article will show you a good method of doing this.
•  A Dynamic SQL is needed when we need to retrieve a set of records based on different search parameters. Say for example - An employee search screen or a general purpose report which needs to execute a different SELECT statement based on a different WHERE clause.

NOTE: Most importantly, the Dynamic SQL Queries in a variable are not compiled, parsed, checked for errors until they are executed.

sp_executesql Vs EXECUTE Command:

• A dynamically build Transact-SQL statements can be executed using EXECUTE Command or sp_executesql statement.
•  Here, in this article in my examples, I'll be using sp_executesql which is more efficient, faster in execution and also supports parameter substitution.
•  If we are using EXECUTE command to execute the SQL String, then all the parameters should be converted to character and made as a part of the Query before execution.
•  But the sp_executesql statement provides a better way of implementing this. It allows us to substitute the parameter values for any parameter specified in the SQL String. 
• Before getting into the actual example, let me differentiate these two commands with a simple example. Say - selecting a record from the employee table using the ID in the WHEREclause.

The basic syntax for using EXECUTE command:

EXECUTE(@SQLStatement)

The basic syntax for using sp_executesql:

sp_executesql [@SQLStatement],[@ParameterDefinitionList],
[@ParameterValueList]

/* Using EXECUTE Command */

/* Build and Execute a Transact-SQL String with a single parameter 

value Using EXECUTE Command */

/* Variable Declaration */

DECLARE @EmpID AS SMALLINT

DECLARE @SQLQuery AS NVARCHAR(500)

/* set the parameter value */

SET @EmpID = 1001

/* Build Transact-SQL String with parameter value */

SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = ' + 

CAST(@EmpID AS NVARCHAR(10))

/* Execute Transact-SQL String */

EXECUTE(@SQLQuery)

• There are two variables declared. The first variable @EmpID is used as a parameter to the SQL Query and second Variable @SQLQuery is used to build the SQL String. You can clearly see that the variable@EmpID is cast to a NVarchar type and made as a part of the SQL String. If you print the @SQLQuery string (PRINT @SQLQuery), you will get the actual SQL query as shown below

SELECT * FROM tblEmployees WHERE EmployeeID = 1001

Finally, the above query is executed using the EXECUTE command.

sp_executesql:

* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

• there are two variables declared. The variable @EmpID is used as a parameter to the SQL Query and second variable @SQLQuery is used to build the SQL String, the third variable @ParameterDefinition is used to specify the parameter format before executing the SQL string. If you print the @SQLQuery string (PRINT @SQLQuery), you will get the query as shown below:

SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID

• Here, in this example, you can clearly see the parameter @EmpID is included in the statement. Finally, sp_executesqltakes the necessary information to do the parameter substitution and execute the dynamically built SQL string.

1. @SQLQuery --> contains the SQL statement
2. @ParameterDefinition --> contains the Parameter Definition
3. @EmpID --> contains the parameter value to be substituted to the parameter in the SQL statement.

NOTE: The parameters included in the Dynamic SQL string must have a corresponding entry in the Parameter Definition List and Parameter Value List.

EXAMPLE:

/* Transact-Sql to create the table tblEmployees */

CREATE TABLE tblEmployees

(

    EmployeeID       SMALLINT IDENTITY(1001,1) NOT NULL,

    EmployeeName     NVARCHAR(100) NOT NULL,

    Department       NVARCHAR(50) NOT NULL,

    Designation      NVARCHAR(50) NOT NULL,

    JoiningDate      DATETIME NOT NULL,

    Salary           DECIMAL(10,2) NOT NULL,

    [Description]    NVARCHAR(1000) NULL 

)

/* Transact SQL to insert some sample records into tblEmployee table */

INSERT INTO tblEmployees

(EmployeeName, Department, Designation, 

 JoiningDate, Salary, [Description]) 

VALUES    

('John Smith', 'IT Research', 'Research Analyst', 

 '02/08/2005', 23000.00, 'Analyst since 2005')

INSERT INTO tblEmployees

(EmployeeName, Department, Designation, 

 JoiningDate, Salary, [Description]) 

VALUES    

('John Micheal', 'IT Operations', 'Manager', 

 '07/15/2007', 15000.00, NULL)

INSERT INTO tblEmployees

(EmployeeName, Department, Designation, 

 JoiningDate, Salary, [Description]) 

VALUES    

('Will Smith', 'IT Support', 'Manager', 

 '05/20/2006', 13000.00, 'Joined last year as IT Support Manager')

 SELECT * FROM tblEmployees