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:
- The parameter name must start with an “@” symbol and cannot have spaces or certain special characters.
- The data type of the parameter and possibly the size must be stated.
- 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.