Return Values: Getting Something Back from Your SQL Server Stored Procedures

The top part is not going to change much. We need to create a procedure, name it, and pass our two login data elements to the stored procedure. We will have to add one more variable, though. We are going to use this variable to store the value that the stored procedure will return (i.e. the “return value”).

CREATE PROCEDURE usp_checkuser

            @login varchar(50),

            @pswd varchar(50),

            @usr_id int

AS

Next, we need to change the SELECT statement. We are only going to select one data element from the USRS table. We are going to put this single value into a variable. Also, we still need the WHERE clause. Remember, we only want to return one data element. We will use the WHERE clause to ensure that the query only returns one value. Before we even work with the SELECT statement, though, we need to set the value of the @usr_id variable.

SET @usr_id =

We are going to store the results of the query in this variable. So, everything the SELECT statement does will have to go inside the variable itself. How do we do this?  A subquery!

SET @usr_id = ()

The SELECT statement will go between the parentheses. The SELECT statement itself will not change much. We still want to query the USRS table, and we still want to use the same “filter” (i.e. the same WHERE clause). But, we do want to return a single value. Consequently, we will return only a single value, id.

SELECT id FROM USRS

            WHERE login=@login

            AND pswd=@pswd

We are still looking for the unique identifier that corresponds to the unique combination of username and password. That combination will yield a single “id” (the primary key of the USRS table). Now, we want to put this query between the parentheses.

SET @usr_id = (SELECT id FROM USRS

            WHERE login=@login

            AND pswd=@pswd)

We are soooo close! The only thing we have left to do is actually return the “return value.”  To do this, use the RETURN function. This function is pretty easy to use. Simply type RETURN, followed by the variable that contains the return value.

RETURN @usr_id

With the new return function, the total procedure will look like this:

CREATE PROCEDURE usp_checkuser

            @login varchar(50),

            @pswd varchar(50),

            @usr_id int

AS

SET @usr_id = (SELECT id FROM USRS

            WHERE login=@login

            AND pswd=@pswd)

RETURN @usr_id

GO

Executing this stored procedure will return the “id” value, based on the unique username and password combination. You will not “see” the return value. It is there, though. Programmatically, you can access this return value (using ASP, Java, Visual Basic, or whatever other programming language you use).

Conclusion

In this article, we have reviewed why return values are useful, and how to include them in your stored procedures. Don’t blink, you might miss it. Seriously. Return values are very easy to use, and are quite powerful. There are two basic elements to return values:  (1) putting your SELECT query results in a variable and (2) using the RETURN function to return your variable contents. Remember, use return values to have the stored procedure kick back a single value, not an entire record. If you need more output from your stored procedure than just a single value, the OUTPUT function would be more appropriate.