Working with Dynamic Stored Procedure.

vishalneeraj-24503
Posted by vishalneeraj-24503 under Sql Server category on | Points: 40 | Views : 900
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.

Comments or Responses

Posted by: Chakravarthi on: 12/4/2013 Level:Starter | Status: [Member] | Points: 10
Hi Vishal,

I am trying this code, it throws an error for me "incorrect syntax near 'AA'" .

If i am using int value like emp_id = 2 in the place of status = 'AA' its working.

Thanks & Regards

Login to post response