Then Dynamic SQL is your answer what your finding for. 

It provides more flexibility to your Query and you can perform all SELECT,INSERT,UPDATE,DELETE operations.

It can perform all functions as same as static or normal query.

For this we need build query into a string form and store it to variable and then need to execute using 'EXECUTE' command.

BASIC SYNTAX: EXECUTE(@Query);

We need to pass the input parameters into Dynamic query as nvarchar using CAST function so it becomes part of string.

Let us look into a Example in SQL Server:

Consider we have a 'student' table 

Id Name Department

1 A CSE

2 B ECE

3 C EEE

4 D IT

5 E MECH


and now we will update the department of 'E' Student to 'CIVIL'

First we will get the ID of the 'E' Student.

we will declare an variable of type int which stores the ID of the student.

//variable declaration

 Declare @id smallint; 

 Declare @dynamicquery nvarchar(250);

Select @id = id from student where name = 'E'; //Variable assignment for @id

Building the Sql command to String form:

SET @dynamicquery = 'UPDATE student SET Department = ''CIVIL'' Where Id = ' + Cast(@id AS NVARCHAR)

Note: we will use two single quotes as it acts as escape character .

 If we print @dynamicquery by using below command

PRINT @dynamicquery ;

Result : UPDATE student SET Department = 'CIVIL' Where Id = 5

//EXECUTE

EXECUTE(@dynamicquery);

To check the results:

select * from student;


Id Name Department

1 A CSE

2 B ECE

3 C EEE

4 D IT

5 E CIVIL

Table is updated.

WE CAN ALSO CREATE AND EXECUTE A DYNAMIC SQL IN A STORED PROCEDURE.

Let us start by considering the Below table as reference and start creating an stored procedure,

Id Name Department

1 A CSE

2 B ECE

3 C EEE

4 D IT

5 E CIVIL

6 F CSE

7 G ECE

8 H EEE

9 I IT

// Creation of SP

Create Procedure sp_Student(@Department nvarchar(50))

AS

BEGIN

Set NoCount ON;

DECLARE @Gensql nvarchar(max);

SET @Gensql = 'Select * from Student Where Department = ' + CAST(@Department AS nvarchar) + 'Order by Name'; //Used order by clause

Execute(@Gensql);

Set NoCount OFF

  Return(0)

END

Now we will execute the SP by below command.

EXEC sp_Student 'CSE' ;

Result :

Id Name Department

1 A CSE

6 F CSE

We will also look into how to call stored procedure dynamically :

Declare @SQLQuery nvarchar(250),@Department nvarchar(50);

set @Department = 'CSE';

@SQLQuery = 'sp_Student @Department = '+ CAST(@Department AS nvarchar)

Execute (@SQLQuery)

Looking into result, it will be same as above.

This is a simple example for our basic understanding of Dynamic Sql but real world solutions are far more complex.

There are disadvantages for this dynamic Sql in which few are listed below.

1. As it is a run time process so error management becomes more unreliable. 

2. We may be able to face security issues as we never know the input values until run time

3. It is difficult to access temporary Tables if they are used inside a stored procedure.