Working with Dynamic Stored Procedure.

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
As

Declare @sql_query nvarchar(max);

Set @sql_query = '';

Begin
Set @sql_query = 'select employee_name,employee_surname,phone_no,address 
                 from
		 employee_master where status = 'AA' order by employee_name';

-- exec(@sql_query);
exec sp_executesql @sql_query;
End

Press F5 to create Stored procedure.

And to execute above stored procedure,in the query window,we will write as

Exec load_employee

Note:- You can see that,exec(@sql_query) is commented inside sp.

There are the 2 way to execute dynamic sp,
1). exec(@sql_query).
2). exec sp_executesql @sql_query.