none
Why Do We Use Stored Procedures?

    Question

  • I have been using MS Access for many years, and now I am trying to learn SQL Server (and hopefully get away from MS Access).  So, I’m reading a book about Stored Procedures.  To me, they seem like Queries in Access, but much more complicated.  For instance, I saw this in my book:

     

    USE Northwind

    SET QUOTED_IDENTIFIER  ON

    GO

    IF OBJECT_ID('dbo.listorders')  IS  NOT  NULL

    DROP PROC dbo .listorders

    GO

    CREATE PROC dbo .listorders

    AS

    SELECT *  FROM "Order Details"

    GO

    SET QUOTED_IDENTIFIER  OFF

    GO

    EXEC dbo .listorders

     

    Now, I run this:

     

    SELECT *  FROM "Order Details"

     

    The results are exactly the same!!  My question has two parts.  Why do we need all the extra stuff in a Stored Procedure, when SQL will do exactly the same thing?  How do you actually learn how to do useful things with Stored Porcedures?  I’m reading a book on this now (TSQL Guru’s Guide to SQL Server Stored Procedures), and I’m not finding much useful information in the book.

     

    Then, just for fun, I run another Stored Procedure:

    USE Northwind

    IF (OBJECT_ID('dbo.ListRegionalEmployees') IS NOT NULL)

    DROP PROC dbo.ListRegionalEmployees

    GO

    SET ANSI_NULLS OFF

    GO

    CREATE PROC dbo.ListRegionalEmployees @region nvarchar(30)

    AS

    SELECT EmployeeID, LastName, FirstName, Region FROM employees

    WHERE Region=@region

    GO

    SET ANSI_NULLS ON

    GO

     

    Message:  Command(s) completed successfully.

     

    I’m thinking, ‘OK!  Great!!’  However, I don’t see anything actually happening.  How can I tell what is going on when I only get a message that the Procudure was successful, but I don’t actually SEE anything at all???

     

     

     Thanks for your input, everyone!!

     

    Tuesday, June 21, 2011 8:34 PM

Answers

  • Hi 

    The above script creates the stored procedure, Its like creating Query in Access, After creating the stored procedure, you need to execute that to get results

    Ex.

    EXEC dbo.ListRegionalEmployees @region = 'WEST'

    GO

    Read this article for why we need to use SPs

    http://searchsqlserver.techtarget.com/news/1052737/Why-use-stored-procedures

    http://blog.sqlauthority.com/2007/04/13/sql-server-stored-procedures-advantages-and-best-advantage/

    http://www.csharpfriends.com/articles/getarticle.aspx?articleid=78


    Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
    If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"
    • Proposed as answer by Naomi NModerator Wednesday, June 22, 2011 3:56 AM
    • Marked as answer by ryguy72 Saturday, June 25, 2011 11:39 PM
    Wednesday, June 22, 2011 2:06 AM
  • Also have a read of the following

    http://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

     


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    • Marked as answer by ryguy72 Saturday, June 25, 2011 11:39 PM
    Wednesday, June 22, 2011 2:28 AM
  • My question has two parts.  Why do we need all the extra stuff in a Stored Procedure, when SQL will do exactly the same thing?  How do you actually learn how to do useful things with Stored Porcedures?    


    Let's tackle these one at a time.

     

    1. Why do we need all the extra stuff in a stored procedure, when SQL will do exactly the same thing?

    To be completely honest in the above examples you have shown in your original post, you wouldn't necessarily need to use them. Stored procedures can be very helpful for many reasons, including network congestion, security, standardized API, abstraction, programibilty, etc. I'll give an example of each.

    Network Congestion: While this really isn't a reason why most people write stored procedures think about a query that could be massively large text such as the following.

    WITH Waits AS
     (SELECT
      wait_type,
      wait_time_ms / 1000.0 AS WaitS,
      (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
      signal_wait_time_ms / 1000.0 AS SignalS,
      waiting_tasks_count AS WaitCount,
      100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
      ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
     FROM sys.dm_os_wait_stats
     WHERE wait_type NOT IN (
      'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
      'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
      'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
      'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
      'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
      'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
      )
    SELECT
      W1.wait_type AS WaitType,
      CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
      CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
      CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
      W1.WaitCount AS WaitCount,
      CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
    FROM Waits AS W1
    INNER JOIN Waits AS W2
      ON W2.RowNum <= W1.RowNum
    GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
    HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
    GO
    
    

    That's a good bit of text to throw in a TDS packet and carry across the network for every person who would run it (assume many people would love to run this concurrently in a large environment). Everytime a user would run this query all of the query text would have to be passed to SQL Server through the network. Now image there was a stored procedure that had this query in it called 'Perf.GetWaitStats'. Now instead of sending that whole big mess of SQL across the wire you're only sending 'EXEC Perf.GetWaitStats', that's a pretty big cost savings in terms of network IO and effiency especially if it's going to be executed several times a minute (please note that this is a random query I grabbed from my script list that was long enough to make sense. People wouldn't actually run this serveral times a second or minute).

     

    Security: This is too long to really discuss and get into here, but I will hit a few points. Using a standard query the user must have direct )or with a view indirect) access to the base tables that the data belongs to. Do you really want a user being able to read and write to your applications base tables? Probably not. If they did, they wouldn't even need your application to do updates, now choosing a linked source inside of Excel, word, or access could easily allow them to update whatever information they would like. Using stored procedues (and indeed straight sql queries) fine grained access can be controlled. Imagine (gross overstatement) if users could only interact with your database through a set of stored procedures and couldn't touch base tables at all...

     

    Standardized API: This is more of a development and administration topic. Stored Procedures allow for developers and administrators alike to use a standardized set of calls (known as API) in order to make applications work without having to re-invent the wheel everytime. For example if you wanted to keep an error log in your database from problems that may arise, it would make sense to create a stored procedure called 'Err.GetErrorLog' that any of the developers could call. This may not sound very helpful at first, but say the developer resposible for writing the error logging decided to change the structure of his logging table. Other developers may not know about this and get an error is they run a query that they made before the changes took place. This way the developer who was working on the error logging can easily update the stored procedure and all of the developers who had the Err.GeErrorLog stored procedure called in their code wouldn't have to re-write it as they would if it had been a query. This also gets into the discussion of plan reuse but I won't talk about that here.

     

    Abstraction: This may or may not be trivial to the end user/developer/administrator but I think it needed to be covered quickly. Abstraction (like the API) allows for developers who may be writing the data middle tier to be abstracted from the base tables or views which allows them to work with their set of stored procedures and not have to worry about changes made (obviously large functional changes they do, but small non-function changes not so much) to base objects whether it be a function or a table. This allows the developer to continue working and not have to re-educate himself everytime a small change to the base table is made, let alone change his code to reflect the changes (which have already been made to the stored procedure).

     

    Programibility: One of the nice things you can do with stored procedures is error trapping and handling through such things as the TRY/CATCH blocks and RAISERROR. This allows your program to behave nicely by logging errors for support to investigate without having your application crash. This is pretty self explanitory in terms of use and I think everyone understands that it is better to handle an error than let it run rampant.

     

    2. How do you actually learn how to do useful things with stored procedures?

    Time, reading, reading, trying, testing, reading more, banging your head against a wall, more reading, sleepless nights, loss of significant other (ok maybe not that far). If you are serious about becoming a SQL Server developer I would highly encourage you to check out the Database developer books published by Microsoft Press. Other resources include various websites and blogs such as this site, books online, sqlskills, sqlservercentral, etc.

     

    Hopefully this helped,
    -Sean

    • Marked as answer by ryguy72 Saturday, June 25, 2011 11:39 PM
    Wednesday, June 22, 2011 2:24 PM

All replies

  • Hi 

    The above script creates the stored procedure, Its like creating Query in Access, After creating the stored procedure, you need to execute that to get results

    Ex.

    EXEC dbo.ListRegionalEmployees @region = 'WEST'

    GO

    Read this article for why we need to use SPs

    http://searchsqlserver.techtarget.com/news/1052737/Why-use-stored-procedures

    http://blog.sqlauthority.com/2007/04/13/sql-server-stored-procedures-advantages-and-best-advantage/

    http://www.csharpfriends.com/articles/getarticle.aspx?articleid=78


    Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
    If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"
    • Proposed as answer by Naomi NModerator Wednesday, June 22, 2011 3:56 AM
    • Marked as answer by ryguy72 Saturday, June 25, 2011 11:39 PM
    Wednesday, June 22, 2011 2:06 AM
  • Also have a read of the following

    http://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

     


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    • Marked as answer by ryguy72 Saturday, June 25, 2011 11:39 PM
    Wednesday, June 22, 2011 2:28 AM
  • My question has two parts.  Why do we need all the extra stuff in a Stored Procedure, when SQL will do exactly the same thing?  How do you actually learn how to do useful things with Stored Porcedures?    


    Let's tackle these one at a time.

     

    1. Why do we need all the extra stuff in a stored procedure, when SQL will do exactly the same thing?

    To be completely honest in the above examples you have shown in your original post, you wouldn't necessarily need to use them. Stored procedures can be very helpful for many reasons, including network congestion, security, standardized API, abstraction, programibilty, etc. I'll give an example of each.

    Network Congestion: While this really isn't a reason why most people write stored procedures think about a query that could be massively large text such as the following.

    WITH Waits AS
     (SELECT
      wait_type,
      wait_time_ms / 1000.0 AS WaitS,
      (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
      signal_wait_time_ms / 1000.0 AS SignalS,
      waiting_tasks_count AS WaitCount,
      100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
      ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
     FROM sys.dm_os_wait_stats
     WHERE wait_type NOT IN (
      'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
      'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
      'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
      'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
      'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
      'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
      )
    SELECT
      W1.wait_type AS WaitType,
      CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
      CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
      CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
      W1.WaitCount AS WaitCount,
      CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
    FROM Waits AS W1
    INNER JOIN Waits AS W2
      ON W2.RowNum <= W1.RowNum
    GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
    HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
    GO
    
    

    That's a good bit of text to throw in a TDS packet and carry across the network for every person who would run it (assume many people would love to run this concurrently in a large environment). Everytime a user would run this query all of the query text would have to be passed to SQL Server through the network. Now image there was a stored procedure that had this query in it called 'Perf.GetWaitStats'. Now instead of sending that whole big mess of SQL across the wire you're only sending 'EXEC Perf.GetWaitStats', that's a pretty big cost savings in terms of network IO and effiency especially if it's going to be executed several times a minute (please note that this is a random query I grabbed from my script list that was long enough to make sense. People wouldn't actually run this serveral times a second or minute).

     

    Security: This is too long to really discuss and get into here, but I will hit a few points. Using a standard query the user must have direct )or with a view indirect) access to the base tables that the data belongs to. Do you really want a user being able to read and write to your applications base tables? Probably not. If they did, they wouldn't even need your application to do updates, now choosing a linked source inside of Excel, word, or access could easily allow them to update whatever information they would like. Using stored procedues (and indeed straight sql queries) fine grained access can be controlled. Imagine (gross overstatement) if users could only interact with your database through a set of stored procedures and couldn't touch base tables at all...

     

    Standardized API: This is more of a development and administration topic. Stored Procedures allow for developers and administrators alike to use a standardized set of calls (known as API) in order to make applications work without having to re-invent the wheel everytime. For example if you wanted to keep an error log in your database from problems that may arise, it would make sense to create a stored procedure called 'Err.GetErrorLog' that any of the developers could call. This may not sound very helpful at first, but say the developer resposible for writing the error logging decided to change the structure of his logging table. Other developers may not know about this and get an error is they run a query that they made before the changes took place. This way the developer who was working on the error logging can easily update the stored procedure and all of the developers who had the Err.GeErrorLog stored procedure called in their code wouldn't have to re-write it as they would if it had been a query. This also gets into the discussion of plan reuse but I won't talk about that here.

     

    Abstraction: This may or may not be trivial to the end user/developer/administrator but I think it needed to be covered quickly. Abstraction (like the API) allows for developers who may be writing the data middle tier to be abstracted from the base tables or views which allows them to work with their set of stored procedures and not have to worry about changes made (obviously large functional changes they do, but small non-function changes not so much) to base objects whether it be a function or a table. This allows the developer to continue working and not have to re-educate himself everytime a small change to the base table is made, let alone change his code to reflect the changes (which have already been made to the stored procedure).

     

    Programibility: One of the nice things you can do with stored procedures is error trapping and handling through such things as the TRY/CATCH blocks and RAISERROR. This allows your program to behave nicely by logging errors for support to investigate without having your application crash. This is pretty self explanitory in terms of use and I think everyone understands that it is better to handle an error than let it run rampant.

     

    2. How do you actually learn how to do useful things with stored procedures?

    Time, reading, reading, trying, testing, reading more, banging your head against a wall, more reading, sleepless nights, loss of significant other (ok maybe not that far). If you are serious about becoming a SQL Server developer I would highly encourage you to check out the Database developer books published by Microsoft Press. Other resources include various websites and blogs such as this site, books online, sqlskills, sqlservercentral, etc.

     

    Hopefully this helped,
    -Sean

    • Marked as answer by ryguy72 Saturday, June 25, 2011 11:39 PM
    Wednesday, June 22, 2011 2:24 PM
  • Thanks everyone, especially Sean; that's a lot of typing!!  Yessssiirrrreeeeee, banging your head against a wall!!  I do a lot of that with MS Access now!!

    All of this is very helpful!!  I have to read up on SQL Server and SProcs, over the weekend, and for many, many, many weekends in the future.


    Friday, June 24, 2011 9:39 PM