SQL Server Stored Procedures – Fundamentals

Exercise:  Pass Data to a SELECT Stored Procedure

Create a stored procedure that returns one record, based on the table’s primary key. Remember to:

  1. Create the header record (commented)

  2. Create the stored procedure name and declare variables

  3. Create the rest of your stored procedure

When you are done, copy your stored procedure into the SQL Server New Stored Procedure window (if you are using a separate text editor), and check the syntax. Also, you may want to open the Query Analyzer and run the execute statement. I’ll provide both the stored procedure and execute statement (with sample data) below.

Answers

Stored Procedure:

/*
Name: usp_finduser
Description:  find a user
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill
*/

CREATE PROCEDURE usp_finduser

@usr_id int

AS

SELECT * FROM USERLIST
WHERE usr_id=@usr_id

Execute Statement:

exec usp_finduser ’1′

Did it work?  If not, keep trying!  You’ll get there.

In Closing

This has been a pretty aggressive lesson. You showed up somewhat familiar with databases, but probably knowing nothing about stored procedures (unless you are a database guru who read my article so you could viciously critique it later!). We have gone from defining stored procedures to writing them independently. That is great!  Stored procedures are an excellent way to insulate your programming logic from the threat of technology migrations in the future. They are useful, make for efficient application development, and are easy to maintain. Using the information and exercises above, you should be on your way to creating stored procedures to support any database-related endeavor.

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 |