Stored procedure with parameters: A guide for beginners

If you need to write a stored procedure within SQL Server, odds are it will be a stored procedure with parameters.

It is much more common to see a stored procedure with parameters than one without parameters. It’s important you understand both input and output parameters, which we will discuss in this tutorial.

I mentioned parameterized stored procedures in my introduction into stored procedures, found here:

SQL Server Stored Procedure: The Ultimate Guide for Beginners

Check out that tutorial if you need to understand the basics about stored procedures.

In this tutorial, we will focus on everything you need to know about writing and calling a stored procedure with parameters.

We will discuss these topics:

1. Writing a stored procedure with parameters
2. Calling the stored procedure
3. Output parameters
4. Default value of a parameter
5. Tips and tricks

Everything in this tutorial can also be found in the following FREE Ebook:

FREE Ebook on SQL Server Stored Procedures!

This FREE Ebook discusses all the key points you need to understand about stored procedures in Microsoft SQL Server. It will definitely be an excellent resource for you to keep and reference throughout your career as a data professional. Make sure you get it today!

Let’s get into it.

1. Writing a stored procedure with parameters

Let’s create some test data first. We’ll create a table called BookCollection, and use a stored procedure to populate it with information.

Here’s our new table:

CREATE TABLE BookCollection
(
BookID INT PRIMARY KEY IDENTITY,
Title VARCHAR(50),
Author VARCHAR(20),
Genre VARCHAR(25)
)

Let’s go ahead and use a regular INSERT statement to add a single row to the table:

INSERT INTO BookCollection (Title, Author, Genre)
VALUES
('1984','Orwell','Fiction')

We’ll run a simple SELECT statement to see this new row in our table:

So far, so good.

We’re going to create a stored procedure to handle the task of inserting values into this table. As parameters to the stored procedure, we’ll pass the data for the new book (Title, Author and Genre). The stored procedure will contain an INSERT statement, and the column values in that INSERT statement will simply be the passed in values from the parameters.

Let’s do it.

A stored procedure with parameters is similar to a function or method in a traditional programming language like C++ or Java

Let’s first look at the basic syntax for creating a stored procedure with parameters:

CREATE PROCEDURE procedureName(<input/output parameters>) 
AS 
<stored procedure body>

The most obvious part we need to break down is the “(<input/output parameters>)” part.

In this section, you’re going to list your input parameters and/or your output parameters.

We are going to focus on input parameters for the moment. We will discuss output parameters in a bit.

Ok, so “what is a parameter?” If you are familiar with programming, you should be familiar with the idea of creating functions (sometimes called methods) that are passed certain variables as arguments.

A function is given a name, and in parentheses next to that name are the arguments being passed to the function. Those arguments have their own name and their data type specified.

Folks, this is pretty much how a stored procedure with parameters works.

You outline the name of the stored procedure, followed by whatever parameters (aka arguments) that will be passed to the procedure. Those parameters serve as variables, complete with their own name and data type.

Now that you get the idea, let’s see how we do it in SQL.

An example of a stored procedure with parameters

Ok, so let’s give it a try. Let’s give our new procedure a name. We’ll call it addNewBook:

CREATE PROCEDURE addNewBook(<input parameters>)
AS
<stored procedure body>

Next, let’s think about what we need to plug in for the <input parameters>. Remember we said we wanted to pass all three values to the stored procedure, which are the Title, Author, and Genre of a book we want to add.

These values will be our parameters. We need to create variables for these parameters, and give each of those variables a data type.

(Remember folks, this is the same thing you do in a traditional programming language when creating a method)

To represent the Title of the book, let’s create a variable called newBookTitle. Variables in SQL Server have the ‘@’ symbol in front of them, so the whole name would actually be @newBookTitle.

For the Author, let’s create a variable called @newBookAuthor.

Finally, for the Genre, we’ll create a variable called @newBookGenre.

Parameter data types

Now, the data types of each. For each variable, we should make the data type consistent. In this case, the value held in each variable will be used as the value inserted into our table. For example, the @newBookTitle will be used to hold the value that will eventually be inserted into the Title column of our table BookCollection.

In other words, since the Title column is a data type of VARCHAR(50), we really ought to make the variable also VARCHAR(50). That way things are consistent. If you wanted, you could give your variable a type of VARCHAR(100), but then you might be opening yourself up for problems later. What do you think will happen when we try to INSERT a value that is 100 characters long into a space that’s only 50 spaces big?

This:

So, let’s finally outline the parameters of our stored procedure. For each parameter, you outline the variable name followed by it’s data type. You separate each parameter with a comma. Like so:

CREATE PROCEDURE addNewBook(@newbookTitle VARCHAR(50), @newBookAuthor VARCHAR(20), @newBookGenre VARCHAR(25))
AS 
<stored procedure body>

Easy peasy.

The body of our stored procedure

The body will be the easy part. Remember, we just want this procedure to perform an INSERT statement into our table. But instead of hard-coded values, we’re going to use the variables we created for the stored procedures.

Here is what our procedure will look like:

CREATE PROCEDURE addNewBook(@newbookTitle VARCHAR(50), @newBookAuthor VARCHAR(20), @newBookGenre VARCHAR(25))
AS
INSERT INTO BookCollection (Title, Author, Genre)
VALUES
(@newbookTitle, @newBookAuthor, @newBookGenre)

So of course, the values inserted into the Title, Author and Genre columns will be whatever values are held in our @newBookTitle, @newBookAuthor and @newBookGenre variables respectively.

Not bad, eh?

2. Calling the stored procedure

Here is where we pull it all together. Calling a stored procedure with parameters is fairly straightfoward.

When we want to call a stored procedure, we use the keyword EXECUTE (which can be abbreviated to just ‘EXEC‘ if you want)

Let’s start with that: EXECUTE addNewBook

So then we need to outline the parameters after the procedure name. For each parameter, you would outline the parameter name, followed by an equals sign, followed by the actual value you want passed to that variable.

Like this: @newBookTitle = 'The Hobbit'

Each parameter would be separated by a comma. So the whole call would look like this:

EXECUTE addNewBook @newBookTitle = 'The Hobbit', @newBookAuthor = 'Tolkien', @newBookGenre = 'Fantasy'

Not so bad, right? If I go ahead and run that EXECUTE statement, we see that our new Book was inserted successfully:

The beauty about this stored procedure is that if we want to insert another book, we just need to change the values passed to our variables:

Pretty slick, right?

Regarding calls to stored procedures:

There is one thing I need to point out about our call to our stored procedure. Notice the parameters aren’t all wrapped within parentheses. This is different from a function call in a traditional programming language. In fact, it’s different from SQL functions, too.

The parentheses are not optional, either. If you try to use parentheses in your procedure call, you will get an error message.

Peep it:

3. Output parameters

Now that you know a thing or two about input parameters, we need to talk about output parameters.

Output parameters were a strange thing for me to understand when I was first learning about stored procedures. Now that I get it, I’m confident I can explain it to you.

Let’s say in the body of our stored procedure, we wanted to return the new BookID of the new book that was just inserted. Remember, the BookID is auto-populated by the handy IDENTITY property. It might be nice to know what BookID was generated for the newest book.

Let’s see what we need to do within the procedure.

1. Outline your OUTPUT parameter in your stored procedure parameter list

The first thing we need to do is add our output parameter to our existing list of parameters. We’ll still outline it’s name, followed by it’s data type, like the other parameters (which we know are input parameters).

But with output parameters, we need to include the keyword ‘OUTPUT‘ next to it.

Basically like so:

ALTER PROCEDURE addNewBook(@newbookTitle VARCHAR(50), @newBookAuthor VARCHAR(20), @newBookGenre VARCHAR(25), @newBookID INT OUTPUT)

See the keyword ‘OUTPUT‘? That’s basically how SQL knows that particular variable (@newBookID) is an output parameter.

Moving on.

2. In the body of your stored procedure, populate the OUTPUT parameter with the value you want returned

This is another easy step. All you need to do is populate your new output parameter with the value you want returned from the procedure.

In our example, we said we wanted to return the newest BookID. Since the BookID column is populated by the IDENTITY property, the easiest way to get the most recent identity value generated is by using the handy SCOPE_IDENTITY() function. This function tells us the last identity value that was generated in the current scope, which is exactly what we want to know!

We just need to write it’s return value to our output parameter.

Check it out:

ALTER PROCEDURE addNewBook(@newbookTitle VARCHAR(50), @newBookAuthor VARCHAR(20), @newBookGenre VARCHAR(25), @newBookID INT OUTPUT)
AS
INSERT INTO BookCollection (Title, Author, Genre)
VALUES
(@newbookTitle, @newBookAuthor, @newBookGenre)

SET @newBookID = SCOPE_IDENTITY()

So again, the @newBookID variable will contain the latest identity value generated in the current scope, which ought to the the identity value that was used in our INSERT statement.

Regarding the body of the stored procedure, that’s all we need to do.

3. When you call the stored procedure, outline a variable that will be used to absorb the return value of your output parameter

This is where things get strange. 

When we call our stored procedure, we still need to outline our output parameter, like this:

EXECUTE addNewBook @newBookTitle = 'The Revenant', @newBookAuthor = 'Punke', @newBookGenre = 'Non Fiction', @newBookID = ???

But what do we put for the value of our output parameter?

We need to put a new variable that will be used to absorb whatever return value is returned from the stored procedure.

I know, it’s strange.

Let’s show you how it’s done. We need to declare a “new variable that will be used to absorb whatever return value is returned from the stored procedure“. Since we know our return value will be an INT, we need to create an absorbing variable that is also an INT. Here’s an example:

DECLARE @newIDfromProc INT

EXECUTE addNewBook @newBookTitle = 'The Revenant', @newBookAuthor = 'Punke', @newBookGenre = 'Non Fiction', @newBookID = @newIDfromProc OUTPUT

Our new @newIDfromProc variable is our absorbing variable.

Notice we also need to outline the keyword OUTPUT again next to it all. Don’t forget that!

So, in a round-about way, the BookID of the newest book will get written to @newIDfromProc.

Pretty weird, right? From there, I can run a simple SELECT statement to see what is in that variable.

Let’s see it in action (using the handy CAST function to convert our integer to a string):

And let’s confirm the data in our table:

Yep, it checks out.

I have a full tutorial on output parameters if you want to learn more:

Stored Procedure Output Parameters: A Guide for Beginners

4. Default Value of a parameter

The last thing we need to talk about is how you can specify a default value for the parameters of your stored procedure.

Maybe we want to create a situation where a user doesn’t need to outline all the parameters when they call our new stored procedure. For example, maybe we don’t necessarily need the ‘Genre‘ right away. Here’s a call to our stored procedure that does not specify a value for the @newBookGenre parameter:

DECLARE @newIDfromProc INT
EXECUTE addNewBook @newBookTitle = 'The Way of Men', @newBookAuthor = 'Donovan', @newBookID = @newIDfromProc OUTPUT

So then the question becomes: “What value will be inserted for the Genre column?”

Back in the definition for our stored procedure, we can ALTER it to contain a default value if a value is not passed in. It’s simple to do. All you do is put the desired default value next to the parameter, like so:

So next to the parameter in question, you put an equals sign (=), followed by whatever default value you want. In our case, we’ll just put a dash for the Genre column.

Now let’s run our EXECUTE statement from earlier and see what was put in our table:

Perfect. 

5. Tips and tricks

Here is a list of tips and tricks you should know when writing a stored procedure with parameters:

Tip # 1. You don’t need to include the name of your parameters when you call the stored procedure. You can just outline the values.

Take a look at this call to our stored procedure:

DECLARE @newIDfromProc INT

EXECUTE addNewBook @newBookTitle = 'Think and Grow Rich', @newBookAuthor = 'Hill', @newBookGenre = 'Development', @newBookID = @newIDfromProc OUTPUT

Understand we don’t need to specify the name’s of the parameters. We can just specify the values. Like this:

DECLARE @newIDfromProc INT

EXECUTE addNewBook 'Think and Grow Rich', 'Hill', 'Development', @newIDfromProc OUTPUT

Let’s see this call in action:

It works fine.

Here’s the thing about this: It’s not very safe in my opinion. When you do this, you need to specify all parameter values, in the correct order. 

If we want the default value to be used for the Genre column, we need to use the DEFAULT keyword in our procedure call:

Personally I don’t like calling stored procedures in this way. When you outline the parameter names, it’s very obvious what value will be placed in what parameter. You can even mix up the order of the parameters and it will still work ok. But if you don’t specify the parameter names, you must outline the values in the correct order they are defined in the procedure. For example, if I mix things up, and accidentally put the Author first, and the Title second, the row will still get inserted, but now we have invalid data in our table:

If we had outlined the parameter names in this call, things would have been fine:

See? It’s all good.

The choice is yours.

Tip # 2. Parentheses in the definition are not required

In the definition of our stored procedure, we wrapped the parameters around parentheses. Understand you don’t need to do this. You can leave off the parameters and it will still work. Like this:

ALTER PROCEDURE addNewBook @newbookTitle VARCHAR(50), @newBookAuthor VARCHAR(20), 
@newBookGenre VARCHAR(25) = '-', @newBookID INT OUTPUT 
AS
INSERT INTO BookCollection (Title, Author, Genre)
VALUES
(@newbookTitle, @newBookAuthor, @newBookGenre)
set @newBookID = SCOPE_IDENTITY()

Personally, I like to keep the parentheses because I think it reads cleaner. It’s something I’m used to doing anyway because of my programming background. Maybe you’ll choose to do the same.

Tip # 3. You can just say CREATE PROC

When creating or altering the definition of the stored procedure, you can abbreviate the word PROCEDURE to just PROC if you want. Like CREATE PROC or ALTER PROC. That will work just fine.

Next Steps:

Leave a comment if you found this tutorial helpful!

Don’t forget to download your FREE Ebook:

FREE Ebook on SQL Server Stored Procedures!

This FREE Ebook discusses all the key points you need to understand about stored procedures in Microsoft SQL Server. It will definitely be an excellent resource for you to keep and reference throughout your career as a data professional. Make sure you get it today!

Make sure you don’t make this mistake in the definition of your stored procedures!

If you missed the introduction tutorial on the topic of stored procedures, definitely check it out. In that tutorial, we discuss everything you need to know about stored procedures, including input and output parameters. Here’s the link:

SQL Server Stored Procedure: The Ultimate Guide for Beginners

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!