Published on

May 10, 2019

Using Parameters with Stored Procedures in SQL Server

The preferred way for applications to access SQL Server data is by the use of stored procedures. The benefits include increased performance, security, and code reuse. However, beginners often find the use of parameters, especially output parameters, with stored procedures confusing. In this article, we will demonstrate how to use parameters with stored procedures, including some sample code using VB.Net.

Creating a Stored Procedure

To create a stored procedure in SQL Server, you can use the following syntax:

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]

A stored procedure can have up to 2,100 parameters, but it is safe to assume that the typical stored procedure has no more than ten. Here is a simple example of a stored procedure that takes two input parameters and one output parameter:

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

Defining Parameters

When defining parameters for a stored procedure, there are a few things to keep in mind:

  1. The parameter name must start with an “@” symbol and cannot have spaces or certain special characters.
  2. The data type of the parameter and possibly the size must be stated.
  3. If you wish to access a return value from the parameter, the OUTPUT keyword must be used.

Parameters can also be given default values. For example, in the previous stored procedure, the “@FirstNumber” parameter has a default value of 5.

Calling a Stored Procedure

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 demonstrates the use of the “usp_AddTwoIntegers” stored procedure:

DECLARE @a int, @b int, @c varchar(30)
SELECT @a = 1, @b = 2
EXEC usp_AddTwoIntegers @a, @b, @c OUTPUT
SELECT @c

The keyword OUTPUT is used in both the calling statement and the procedure’s definition. The arguments can be defined as variables with different names, as long as their positions match. Literal values can also be passed as arguments, and the order of the arguments can be mixed up if the parameters are named in the calling statement.

Accessing Return Values

In addition to output parameters, stored procedures can also return a value. To access the return value, you can use the following syntax:

DECLARE @sum int, @Answer varchar(30)
EXEC @sum = usp_AddTwoIntegers 5, 5, @Answer
SELECT @sum

Return values are typically used to report the success or failure of the procedure. For example, a return value of 0 may indicate success, while a return value of 1 may indicate failure.

Calling a Stored Procedure from VB.Net

Here is a code snippet showing how to call a stored procedure using VB.Net:

Imports System.Data.SqlClient

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

Conclusion

Using stored procedures is the best way for applications to access SQL data. This article explained, using a simple example, how to use parameters with stored procedures. By understanding the concepts and syntax, developers can leverage the power and flexibility of stored procedures in their SQL Server applications.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.