By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 2,004 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

Stored Procedure with parameters Vs direct Query

P: 45
Hi guys,

1. In my application,to Connect to DB, i used to write direct string queries in any function, assigning them appropriate variables using '+' sign. Then I had separate Data Access class to which I used to pass this query where it used to get executed.Code was like this:

Expand|Select|Wrap|Line Numbers
  1.   void someFunction()
  2.      { 
  3.           clsDatabase objDB = new clsDatabase();
  4.           strSQL = "INSERT INTO QUERY"+param1;
  5.           objDB.ProcessQuery(strSQL);
  6.      }
Now, I have changed my way, I have written a stored procedure with parameters. I call a function of Data Access class which calls this stored procedure. I pass parameters to function which are assigned to strored procedure parameters.

Expand|Select|Wrap|Line Numbers
  1.   void someFunction()
  2.      { 
  3.           clsDatabase objDB = new clsDatabase();
  4.           objDB.functionWhichCallsStoredParameters(param1);
  5.      }
Both approches work, but i want to know which one is good & faster.

2. Second Thing I want to know is, In one of my classes, I m making object of ClsDatabase in static method. I think its gonna use same objects again & again, creating problems for concurrency.Plz, clarify.

Expand|Select|Wrap|Line Numbers
  1.   static void someFunction()
  2.      { 
  3.           clsDatabase objDB = new clsDatabase();
  4.           objDB.functionWhichCallsStoredParameters(param1);
  5.      }

Thanks in advance.
Apr 27 '10 #1
Share this Question
Share on Google+
3 Replies


P: 45
Please, Let me know if my question is not clear to u guys...no reply so far...???
Apr 27 '10 #2

ck9663
Expert 2.5K+
P: 2,878
On your first question, the second one, by default is usually faster.Even if the query are exactly the same. This is because SQL Server stores the way it executed the stored procedure using the optimizer. If you use a dynamic sql, it will run it as if it is executed the first time and the way it will be executed might not always be the most efficient way to execute the query.

Your second question is more of a front-end question than a back-end question.

Happy Coding!!!

~~ CK
Apr 27 '10 #3

debasisdas
Expert 5K+
P: 8,127
In any database the stored procedure is a pre-compiled object. The SQL part in the stored procedure is not parsed / compiled at run time which is thecase with any raw SQL. Thats why stored procedure will always give you better performance.
Apr 28 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.