Share via

SQL Question...to sproc or not to sproc???

  • Article

So how does a person know when the best time is to use a stored procedure vs a normal sql statment?  For example....I created a test database and made a stored procedure to return all the rows of my table.  Then I made a C# function that called that stored procedure and put the results into a DataTable.  Now I know I could have just done the exact same thing by setting the CommandText to the same line that the stored procedure uses.

The only reason I can think of is if I needed to pass in a parameter.  I don't think you can do that with using a regular sql statement.

Comments

  • Anonymous
    March 07, 2005
    The comment has been removed
  • Anonymous
    March 07, 2005
    The comment has been removed
  • Anonymous
    March 07, 2005
    The comment has been removed
  • Anonymous
    March 07, 2005
    You can use parameters when using non-SP SQL (bare SELECT, UPDATE and INSERT).

    I would certainly use an SP when it might save round trips to the server. I would also use an SP when I need to do complex bit of processing that is best done on the database server.

    In general, I always use SP's just for sonsistancy.
  • Anonymous
    March 07, 2005
    The comment has been removed
  • Anonymous
    March 07, 2005
    I agree that stored procs helps with security but they lend so much else to the architecture of your application. I try to always use stored procs to separate the presentation and business layers from data layer. I do not want them to know anything about the data structures of the data layer. All I want them to do is know what to call to get the data. They do not care from where the data comes from and how it was given to them. Also makes it easier to move your application to another data engine.

    My rule is to let the data layer worry about storing the data, the business layer worry about what to do with the data and the presentation layer worry about showing the data.
  • Anonymous
    March 07, 2005
    >In general, I always use SP's just for sonsistancy.

    ..But not for spell-checking, apparently. <g>
  • Anonymous
    March 07, 2005
    SQL Injection is where someone enters carefully crafted text that allows the user to do bad things in the database.

    http://www.4guysfromrolla.com/webtech/061902-1.shtml#postadlink

  • Anonymous
    March 07, 2005
    You definitely have to worry about SQL-injection attacks when you're on private intranet. Does it mean that all your users are entitled to all your data? Does it mean that all of them behave well? You should consider all user input as hostile.

    What is SQL injection?

    For example, you log-on your user by counting rows returned by the query

    "SELECT * FROM Users WHERE username='" + username + "' AND Password='" + Password + "'"

    This query is vulnerable to SQL injection. If malicious user supply username
    admin
    and password
    ' OR 1=1--

    you'll get the following query:

    SELECT * FROM Users WHERE username='admin' AND Password='' OR 1=1 --'

    which will of course return more than 0 rows, without providing a valid password. And there is a lot other options, including using UNION statements for direct retrieving data from arbitrary tables... Use your imagination ;)

    So, you always should worry about SQL Injection and use Parameters collection - no matter if you do it using ad-hoc query, or stored procedure.
  • Anonymous
    March 07, 2005
    Thanks everyone for your input. I was trying to answer a design question to a co-worker who felt it would just be a lot more simple to make a function that used a parameterized query rather than going through the bother of making a stored procedure.

    I think with all the response I've gotten, I'm totally on the making a stored procedure side of it. :)
  • Anonymous
    March 07, 2005
    Mharr, you caught me. I am a terrible speller and typist.

    I would not use SP's if database independence was important to me (it is not in most of my work).
  • Anonymous
    March 07, 2005
    The comment has been removed
  • Anonymous
    March 07, 2005
    The comment has been removed
  • Anonymous
    March 07, 2005
    I am really surprised that at all this tangential talk about security, the reason you want an SP in 99.9% of the cases when using a database is for efficiency. The database will pre-compile your SP but cannot do that for a statement.
  • Anonymous
    March 07, 2005
    "The database will pre-compile your SP but cannot do that for a statement."
    No, that's a myth. SqlServer for example doesn't pre-compile your proc. Only DB2 does (to C code).
  • Anonymous
    March 07, 2005
    The comment has been removed
  • Anonymous
    March 07, 2005
    Theserverside.net hosted a debate between me and a wintellect guy about procs vs. dyn. sql:
    http://www.theserverside.net/news/thread.tss?thread_id=31953

    An old posting by me which is quoted a lot in pro/con proc debates:
    http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
    Enough reading pleasure I think ;)
  • Anonymous
    March 07, 2005
    The biggest advantage of stored procedures is that they force you to think about your data structure more and encourage good practices. Imagine that you come up with a great idea of making a different table for each user of your app. It will increase speed because there won't be as many rows in each table. You will have a much harder time doing this if you are forced to use stored procedures. (If this example seems too off the wall, read the sql news groups, at least a couple posts a week about someone trying to do this.)
  • Anonymous
    March 07, 2005
    More on Sql injection attacks since it's definately important.

    http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/default.aspx

    ask a coworker about them.

  • Anonymous
    March 07, 2005
    The comment has been removed
  • Anonymous
    March 07, 2005
    The comment has been removed
  • Anonymous
    March 07, 2005
    From two comments above I see 2 different opinions that SQL dosen't precompile your sproc only DB2 does? While the second one argues that it does can anyone shed some light on this please.
  • Anonymous
    March 07, 2005
    The comment has been removed
  • Anonymous
    March 08, 2005
    My bad! That's Digital Black Belt

    www.digitalblackbelt.com

    Sorry 'bout that.
  • Anonymous
    June 14, 2005
    I've found more often than not, embedding SQL into code is a maintenance nightmare. Something wrong with your SQL statement? Too bad, you have to recompile and test again. What if your database server moves? What if your tables change around? There's so many things that can go wrong if you're using embedded SQL. It makes much more sense to use a view or stored procedure. At least that's my $0.02 as somebody who has taken both routes.