Working with Dynamic Stored Procedure.

Posted by vishalneeraj-24503 under Sql Server category on | Points: 40 | Views : 909
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(@sql_query);
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

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