Dynamic Stored procedures,whether to create in the application code behind/database?

Question

Hi friends,
I want to create a dynamic stored procedure. Whether i should create it in the databse or can i create in in my dataaccess layer, i need your guidance please

Jean Paul · Accepted Answer

I think creating in the application db layer is better, because the application code and sp will be latest.
If we do this in the database, then the database version should be updated.
(After all as it is dynamic stored procedure with more chances to be changed, needs flexibility in changing - placing in application db layer is better)
After all, this is my first thoughts..

Jean Paul · Answer

Yes.  I can provide you some of the links based on that.
My idea is to have the following steps:
1) Modify the Stored Procedure if it already exists.
2) Create the Stored Procedure if not exists
We can use IF EXISTS, CREATE PROC statements for that.
Please refer the following:
http://msdn.microsoft.com/en-us/magazine/cc188749.aspx

Nethra R S · Answer

Yes,Thanks once again. I would be grateful if you can give an example how to create dynamic stored procedure please.

Jean Paul · Answer

You are Welcome Nethra..
Can I request you to mark the post as answer if your query resolved.

Nethra R S · Answer

Hi Jean,
Thanks, I will create the dynamic stored procedure in dataaccess layer