Using Parameters with Stored Procedures

Introduction

The prefered way for applications to access SQL Server data is by the use of stored procedures. The benefits include increased performance, security, and code reuse. Often, beginning SQL Server developers find the use of parameters, especially output parameters, with stored procedures confusing. This article will demonstrate how to use parameters with stored procedures, including some sample code using VB.Net. The examples shown are based on SQL 2000, but the concepts apply to SQL 2005 as well.

Creating a stored procedure

From Books Online, here is the syntax used to create a stored procedure:

CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]

[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

You can also use the alter procedure statement to modify an existing stored procedure. A stored procedure can have up to 2,100 parameters. It is probably safe to assume that the typical stored procedure has no more than ten.

Here is a very simple create procedure statement that takes two input parameters and one output parameter. Normally, a stored procedure will perform some kind of data manipulation or retrieval, so this is not the average example. It will, however, serve as a simple way to illustrate the use of parameters.

CREATE PROC usp_AddTwoIntegers @FirstNumber int = 5,
   @SecondNumber int, @Answer varchar(30) OUTPUT as

   Declare @sum int
   Set @sum = @FirstNumber + @SecondNumber
   Set @Answer = 'The answer is ' + convert(varchar,@sum)
   Return @sum

How to define a paramter

The first thing to remember is that the parameter name must start with an @ symbol. The name can’t have spaces, and there are some characters that are not allowed. If you just stick with alpha-numeric characters and underscores in your parameter names you won’t have any problems naming parameters.

Second, the data type of the parameter and possibly the size must be stated. For example, the size of a parameter of type VARCHAR must be specified. Third, if you wish to access a return value from the parameter, the OUTPUT keyword must be used. In the example create proc statement, three parameters are defined, two are integers. One of the parameters, @Answer, is an OUTPUT parameter and will be used to pass a value back to the caller.

Parameters can be given a default value. The @FirstNumber parameter has a default value of 5.

How to Call a Stored Procedure from Query Analyzer

A stored procedure can be called from query analyzer, another stored procedure or any other client that can talk to SQL Server. Here is a sample script that can be pasted into Query Analyzer to demonstrate the use of our proc:

Declare @a int, @b int, @c varchar(30)
Select @a = 1, @b = 2
Exec usp_AddTwoIntegers @a, @b, @c OUTPUT
Select @c


---------------
The answer is 3

Notice that the keyword OUTPUT is used in the calling statement as well as the proc's definition. All of the arguments were defined as variables but given names that don’t match the original parameter names. This works because SQL resolves the parameters based on the position.

Only the output parameter must be defined up front. The next example passes literal values for the first two arguments.

Declare @Answer varchar(30)
Exec usp_AddTwoIntegers 1,2,@Answer OUTPUT
Select @Answer


---------------
The answer is 3

It is also possible to mix up the order of the arguments if the parameters are named in the calling statement.

The @FirstNumber parameter has a default that the proc will use since the argument is not specified.

Declare @Answer varchar(30)
Exec usp_AddTwoIntegers @Answer = @Answer OUTPUT, @SecondNumber = 5
Select @Answer


----------------
The answer is 10

Hopefully many of you have noticed that the actual numeric sum is also returned from the procedure. This example shows how to access the return value.

Declare @sum int, @Answer varchar(30)
Exec @sum = usp_AddTwoIntegers 5,5,@Answer
Select @sum


-----------
10

Normally return values are used to report the success or failure of the procedure. So, even though you can return any integer value from the proc, usually a 0 for success or 1 for failure is returned.

Stored procedures are usually used to retrieve or manipulate data. Here is a typical stored procedure using the Northwind sample database that ships with SQL 2000. The parameter is used in the where clause to filter the results.

Create proc usp_GetCustomerName @CustomerID varchar(5) as
      Select CompanyName from Customers where CustomerID = @CustomerID

go

exec usp_GetCustomerName 'ANTON'

CompanyName
-----------------------
Antonio Moreno Taquería

Calling a stored procedure from VB.Net

As promised, here is a code snippet showing how to call a stored procedure using VB.Net. Again, no one would ever call a stored procedure to add two numbers together. This example is given to illustrate the use of parameters.

Create a new VB.Net Windows Application project. Add a button to the form. Add this statement to top of the form’s code window:

	Imports System.Data.SqlClient

Paste this code into the form’s code window right above the End Class statement. I added the sample stored procedure to the Northwind database. Modify the connection string so it will work in your environment.

Private Sub Button1_Click(ByVal sender As System.Object, _
			ByVal e As System.EventArgs) Handles Button1.Click

        'You may need to modify the connection string
        Dim conn As New SqlConnection("Integrated Security=SSPI;" _
			& "Persist Security Info=False;Initial Catalog=Northwind;" _
			& "Data Source=(local);Packet Size=4096;")
        conn.Open()

        'set up the command object
        Dim myCommand As New SqlCommand("usp_AddTwoIntegers", conn)
        myCommand.CommandType = CommandType.StoredProcedure

        'add the first two parameters
        myCommand.Parameters.Add("@FirstNumber", 5) 'this one is optional
        myCommand.Parameters.Add("@SecondNumber", 5)

        'The output and return parameters must be created as objects
        Dim myAnswer As New SqlParameter("@Answer", SqlDbType.VarChar, 30)
        myAnswer.Direction = ParameterDirection.Output

        Dim mySum As New SqlParameter()
        mySum.Direction = ParameterDirection.ReturnValue

        'add them to the parameter collection
        myCommand.Parameters.Add(myAnswer)
        myCommand.Parameters.Add(mySum)

        'execute the query
        myCommand.ExecuteNonQuery()

        'display the values
        MsgBox(myAnswer.Value)
        MsgBox(mySum.Value)
    End Sub

Run the form and click the button to see the output parameter and return values.

Conclusion

Using stored procedures is the best way for applications to access SQL data. This article explained, using a very simple example, how to use parameters with stored procedures.