Explore BrainMass

Explore BrainMass

    Stored Procedures in SQL Server

    Not what you're looking for? Search our solutions OR ask your own Custom question.

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    Stored procedures in SQL 2008 can be written in a variety of languages.
    • Why do you think that it would be important to have the flexibility of using a variety of languages in the development of database systems?
    • How would you use a stored procedure?
    • Provide an example of a stored procedure that you might write to demonstrate why this capability is important.
    • Would you ever want to use a temporary stored procedure? Why or why not?
    • What are some differences in coding when creating a stored procedure in SQL Server vs. Oracle?

    The responses to these questions can be in short paragraphs as it is for a discussion board. Please provide references in APA style if needed. Let me know if you have any questions.

    © BrainMass Inc. brainmass.com December 24, 2021, 11:52 pm ad1c9bdddf

    SOLUTION This solution is FREE courtesy of BrainMass!

    The flexibility of using a variety of languages is important because some languages are better at certain tasks than others. Some languages may be better than others at list processing, some better at controlling hardware, some better at accessing databases or the web, some better at object-oriented programming, etc. Having the flexibility of choosing from different languages allows us to use the language that is most suited to the task at hand.

    Stored procedures play an important role in the software design concept of code reuse. If there is a certain process that needs to be done repeatedly with information in the database, using a stored procedure is the way to go. Stored procedures are also used if more advanced processing in general needs to be done with database information. Although database information can be directly accessed from, for example, Java or C, stored procedures will allow much more to be done than what can be done directly from Java or C, or at the very least will allow the same tasks to be done with significantly more ease.

    One example of using a stored procedure is to gather address information for a customer or member. Suppose there is an automated process to generate letters for customers or members. This process may run mainly through Java, for example. The letter will need to have customer/member address information on it so it can be mailed. This address information is likely stored in a database and likely not in a format where it can be directly placed on the letter as it appears in the database. Rather than putting the address together one piece at a time with Java, a stored procedure can be called to gather the address information and put it in the proper format much more efficiently. The formatted address can then be returned to the Java method so it an be placed on the letter.

    Although most of the time permanent stored procedures are the ideal choice, temporary stored procedures can also be useful. If there is a one-time need for a very specific type of processing, and the existing codebase is not sufficient to do it, then a temporary stored procedure would be better. A specific example would be if we want to use a stored procedure to populate a new table for the first time. Since we would really only do this once, there's no need for a permanent stored procedure.

    There are some big differences behind the scenes (transactions, locking, etc.) and a few coding differences here and there. It is strongly recommended that SQL Server stored procedures use "SET NOCOUNT ON" to suppress the "## rows affected" message in order to save processing time. Oracle doesn't really have an explicit similar command. Another example of a coding difference is Oracle's "CREATE OR REPLACE PROCEDURE" command. SQL Server doesn't have a corresponding "OR REPLACE" feature. Procedures must first be dropped and then recreated. There are actually subtle ways around this which may be used to avoid dropping a procedure, thereby also losing the associated privileges from any GRANT statements, but the point is that SQL Server doesn't have an explicit "OR REPLACE" command. Other coding differences are how the procedure parameters are specified and how local variables are declared inside the procedures.

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    © BrainMass Inc. brainmass.com December 24, 2021, 11:52 pm ad1c9bdddf>