SQL Server Stored Procedures – Fundamentals

Learning something new is challenging. Learning something new on your own redefines challenging.

Chances are, you are reading my article because you want to learn how to write stored procedures. You are new to this, and you don’t know where to start. You are exactly where I was when I wanted to learn how to write stored procedures. The purpose of this article is to help the developer who doesn’t know where to start. I will give you a place to start, teach you techniques beyond the basic “hello world”, and walk you through examples. I learned how to write stored procedures alone, and have decided to let you use my experiences to your advantage. This article will consist of the following topics:

  1. What are stored procedures?

  2. What do you need to get started?

  3. Writing your first stored procedure

  4. Writing more sophisticated stored procedures

    1. Input Variables

    2. Input Variables with SELECT and UPDATE Statements

    3. Exercise:  Pass Data to a SELECT Stored Procedure

    4. Conclusion

    5. Database script for SPFORBEGINNERS (the database referenced in this article)

    At the end, I have also included a SQL script for creating the database used in my examples. It is a small database, and easily replicated. Of course, I am interested in receiving any feedback at tomoneill@deloitte.com.

    What Are Stored Procedures?

    Have you ever written SQL statements, like inserts, selects, and updates? Then you have already written most of a stored procedure. A stored procedure is an already written SQL statement that is saved in the database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database’s command environment (I am a SQL Server kind of guy, and run stored procedures from the Query Analyzer), using the exec command.

    An example is:

    exec usp_displayallusers

    The name of the stored procedure is “usp_displayallusers”, and “exec” tells SQL Server to execute the code in the stored procedure. (Note: “usp_” in front of the stored procedure name is used to designate this stored procedure as a user-created stored procedure.) The code inside the stored procedure can be something as simple as:

    SELECT * FROM USERLIST

    This “select” statement will return all data in the USERLIST table. You may think, skeptically, that stored procedures aren’t terribly useful. Just save the query and run it when you need to. Too easy, right?

    Well, there is more to the story. Many queries get more complex than “select * from . . .”  Also, you may want to call the stored procedure from an application, such as an ASP page, Visual Basic application, or a Java servlet. With a stored procedure, you can store all the logic in the database, and use a simple command to call the stored procedure. Later, if you decide to migrate from ASP to J2EE, you only need to change the application layer, which will be significantly easier. Much of the business logic will remain in the database.

    Enough background—let’s write some stored procedures.

    Getting Started with Stored Procedures

    What do I need to get started? I have heard that question often. To begin writing stored procedures, the following are essential:

    1. A database management system.

    2. A database built inside the database management system (see the end of this article for a sample).

    3. A text editor, such as Notepad or Query Analyzer.

    Items 1 and 2 are absolutely essential. You can’t write stored procedures without a database. They would be useless. Sometimes, I write my procedures in Notepad (or another text editor), and copy them into the New Stored Procedure window in SQL Server. The New Stored Procedure window is a bit small, and in Notepad I can spread things out a bit (you’ll see later).

    Next, you will have to decide what you want your stored procedure to do. It can be tempting to just dive right into the task at hand, but it is always prudent to sketch out some ideas first. Some considerations should be:

    • Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)?

    • With which tables will you have to work? Does it make sense to create a VIEW first?

    • How often will this procedure actually be used?

    Once you have struggled with these questions (something of an exaggeration, I guess), you will be ready to start coding!

    Note: Throughout this article, I will focus on stored procedures for SQL Server. You can apply the same principles to other database management systems, but I will make clear references to working in a SQL Server environment.

    Continues…



Related Articles :

  • No Related Articles Found

9 Responses to “SQL Server Stored Procedures – Fundamentals”

  1. creating and calling stored procedure in .net:

    create procedure insert1(@userid int,@username varchar(50))
    as
    begin
    insert into emp(id,name)values(@userid,@username)
    end

    NOTE:(id,name) are the columns of table in which you want to insert data.

    CALLING STORED PROCEDURE :

    initialize this globally:
    public partial class1:class
    {
    sqlconnection con;
    sqlcommand cmd;
    }

    on page_load(): // write on page load()
    {
    con=new sqlconnection(“Connection string”);
    cmd=new sqlcommand();
    cmd.connection=con;
    cmd.commandtype=commandtype.stored procedure;
    }

    on button_click() // write on insert button_clik event

    {
    cmd.parameters.clear();
    cmd.commandtext=”insert1″;
    // stored procedure name just created.
    cmd.parameters.addwithvalue(“@userid”,textbox1.text);

    cmd.parameters.addwithvalue(“@username”,textbox2.text);
    con.open();
    cmd.executenonquery();
    messagebox.show(“stored procedure inserted…”);
    con.close();
    }

  2. Hi Tom,

    I was reading your article and I would like to appreciate you for making it very simple and understandable. This article gives me a basic idea of stored procedure and it will help me a lot.

    Thank you very much!
    Bhuvan

  3. Hi Tom
    Your article are really awesome.actually i was in search for some good articles on stored procedures and finally i got one.
    The most important is the simplicity which will be very helpful for the beginners.

    Thanks
    Avinash

  4. Hi Tom,

    This was a really informative article for us beginner SQL developers. The procedure was not very complex and easy to understand. I have been studying from a high level SQL developer in order to understand SQL better and he very much confused me with the very advanced code that he was using. Example of more complex code

    /*
    declare @today datetime
    SET @Today = getdate()
    EXECUTE sproc_insertvendor @name = ‘something Else’, @countofpayments = 3, @lastpaydate = @Today, @nextpaydate = @Today, @Comments = null
    */

    IF EXISTS (SELECT * FROM SYSOBJECTS WHERE Name = ‘sproc_insertvendor’)
    DROP PROCEDURE sproc_insertvendor
    GO

    CREATE PROCEDURE sproc_insertvendor
    @name varchar(200),
    @countOfPayments int,
    @LastPayDate datetime,
    @nextpaydate datetime,
    @comments ntext
    AS

    if exists (
    select *
    from vendor v
    where v.name = @name
    )
    BEGIN
    Raiserror(‘%s already exists!’,16,2,@name)
    END

    if @@error=0
    BEGIN
    INSERT INTO vendor (Name,CountOfPayments,LastPayDate,NextPayDate,Comments)
    VALUES (@Name,@CountOfPayments,@LastPayDate,@NextPayDate,@Comments)
    SELECT @@Identity
    END

    RETURN

    GO

  5. I forgot to thank “Om Mohokar” for his post, cheers mate.

  6. Hi Tom,

    This is the best article I have ever found online. Your instruction is extremely easy and helpfulfor me to understand and use for my work.
    Truly appreciate you!
    Best regards,
    Patrick

  7. Unfortunately, far more complex stored procedures have been written, Bryan, but I will walk through it quickly.

    The first two lines are an example of how to declare variables in your stored procedure. DECLARE…uh, declares the variable. SET assigns the variable with a value. You can also do something like SELECT 1 INTO FROM .

    The block that the first lines belong too just serve as an example. Other than the variables it is much like this article explained. Obviously, in the third line, the parameter declaration has values assigned to them.

    The second block is searching for an existing version of a stored procedure with the name ‘sproc_insertvendor’. If it exists, then drop it so that you can create your new one or your new version if you are running this more than once.

    Nothing really new in the third block although I would like to add a side note. To make a parameter optional, you can add a default value (e.g. @countOfPayments INT = 0) which is extremely valuable information to me at least.

    The fourth block introduces you to Raiserror. I don’t personally know if this is built into SQL Server or not, but it is setting the @@error variable. @@ delineates system or global variables the best I can tell.

    Finally, the fifth block is inserting a row as long as the person doesn’t already exist.

    One last side note: try not to write if exists(select *… If you just wanna know if it exists, if exists(select 1… is more performant and gives you the same information you wanted to know.

    Good article, author. I need to find one for advanced beginners now.

  8. Thanks but one important part not mentioned here is “how to modify the stored procedure and save it as the same stored procedure?”

    Right click on the edited query editor page and execute it to save the changes to the stored procedure.(when you change and try to close it prompts you to change as a query, but not as the stored procedure.

  9. Best explanation about stored I have come accross until now.
    @toughdeep:
    here is a syntax to modify a store procedure:

    Alter procedure usp_procedurename
    as
    select*from table
    go.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |