Hi,it's very easy to work with dynamic sp in sql-server.
Sometimes it's required to make a dynamic sp.
Suppose,we want sometimes TOP 50 records or 100 records,then in that case we will write Dynamic SPs or sometimes we want to get records from different Tables.
To understand it,we will make a dynamic stored procedure named Load_Employee:-
Create Procedure Load_Employee
Declare @sql_query nvarchar(max);
Set @sql_query = '';
Set @sql_query = 'select employee_name,employee_surname,phone_no,address
employee_master where status = 'AA' order by employee_name';
exec sp_executesql @sql_query;
Press F5 to create Stored procedure.
And to execute above stored procedure,in the query window,we will write as
Note:- You can see that,exec(@sql_query) is commented inside sp.
There are the 2 way to execute dynamic sp,
2). exec sp_executesql @sql_query.