How to Select Server Output Variables

An SQL Server stored procedure can return one or more output variables, which are parameters in the stored procedure. You need to use the JDBC driver in order to call this type of stored procedure. Output variables behave similarly to input parameters but are declared with the OUTPUT keyword (OUT for short). You need to specify the OUTPUT keyword when executing a stored procedure containing the output parameters. You also need to specify the data type for the parameter and execute the parameter using its name in the stored procedure.

Instructions

1. Click “Start,” “All Programs” and “SQL Server Management Studio.” Enter your credentials at the dialog prompt to connect to the database. Click “New query” to enter the query interface.

2. As an example, create a procedure to get certain students’ email addresses via code:

   CREATE PROC GetStudentidFromEmail (@EmailAddress NVARCHAR(512))
   AS
   SELECT student_id
   FROM dbo.Students
   WHERE StudentsAddress=@EmailAddress
   GO

3. Alter the procedure using output parameters, which enable you to capture the output of the stored procedure in a local variable. Alter the procedure to use an output variable:

   ALTER PROC GetStudentidFromEmail (
   @EmailAddress NVARCHAR (512)
   @Studentid INT OUT
   AS
   SELECT student_id
   FROM dbo.Students
   WHERE StudentsAddress=@EmailAddress
   GO

4. Append OUT to the local variable “@id” in the procedure execution to capture the value of the OUTPUT parameter “@Studentid.”

   DECLARE @id INT
   EXEC dbo. GetStudentidFromEmail
   @EmailAddress =’[e-mail]’
   @Studentid=@id OUT
   SELECT @id

References

• Microsoft: Using a Stored Procedure with Output Parameters