Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kamii47
Constraint Violating Yak Guru
353 Posts |
 Posted - 2010-02-12 : 02:38:58
|
| My question is how would i decide that i should use inline query or Stored procedure when i m retrieving data from Database (except performance issue).Secondly if i need to make a choice between Stored procedure or Function how would i decide that i should use Stored procedure or Function when i m retrieving data from Database.P.S[this question was posted on a forum by one of my friend.I thought it could be answer better here]Kamran ShahidSr. Software EngineerAssurety Consulting inc.(MCSD.Net,MCPD.net) |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-12 : 03:38:08
|
| Stored Procedures offer lots of advantages.Performance. (Why is that not an issue for you?) Query Plan is cached for the Sproc.Security. User only needs Execute permission on the Sproc, the user can have NO permissions on the actual tables.Separation of logic. We can test a fix, or improvement, to an Sproc in isolation and roll it out (and revert to earlier verison if it then fails). All relatively easily.If we want to issue a new version of an ASP file (say) that has to go through rigorous testing, it contains logic for lots of different changed - all in the same file. Much more complex to manage rollouts of small, incremental, changes - or fixes.Inline query:Usually less efficient. Query plan unlikley to be cached, although SQL 2008 has yet more features to help try to cache inline SQL (which just means that it spends even most CPU time trying to work out if some adhoc SQL is parameterisable in a way that is already in the cache)Higher risks of SQL Injection (you have to remember to double up all embedded single quotes every single time you use string concatenation on data that came from the user.Harder to profile and optimise (cannot easily run the SQL on its own with SHOW PLAN or SHOW STATSTICSRequires permissions on the underlying tables (as mentioned above)Makes it much easier to write queries that are "flexible" - user selects criteria from a long list of available options, and choose complex sort orders, and from that string concatenation is used to make a suitable SQL query.Some of these issues can be offset by using parameterised queries, or calling sp_ExecuteSQL ... but once you've gone that far in most cases you would be better off having started off with Sprocs. |
 |
|
|
kamii47
Constraint Violating Yak Guru
353 Posts |
Posted - 2010-02-12 : 06:00:11
|
| Very nicely explained Kirsten.Can any body also give light with SP's and function perspectiveKamran ShahidSr. Software Engineer(MCSD.Net,MCPD.net) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-12 : 06:07:44
|
| A function is just a replacement for a VIEW- i.e. you reference it as you would a View or a Table, but it takes parameters and can thus perform more complex tasks than a VIEW.I don't see any point using Functions from the application. A Stored Procedure might use them to centralise certain logic to avoid repeating it in several different places, but if you are not going to have SProcs and use adhoc inline SQL why go to the point of writing functions and NOT go the further step of writing SProcs?(Having said that you COULD of course, there is nothing stopping you ...) |
|
|
|
|
|
|
|
|
Deciding [inline query or SP?], [SP or Function?]