Page 1 of 1

Stored Procedure (Input, Output, Return Value)

#1 andrewsw  Icon User is offline

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,562
  • Joined: 12-December 12

Posted 28 January 2016 - 03:34 AM

Demonstrates how to create, test and execute an MS SQL Server stored procedure. It also demonstrates the use of input and output parameters, and a return value. (Everything demonstrated is similar for most databases other than SQL Server.)

To an extent it also demonstrates Progressive Testing as I indicate how the code was created and tested in stages.

Stored procedure :wikipedia
SQL Stored Procedures :technet
Stored Procedures :msdn

The TechNet article is old but still a useful first read:
"A stored procedure is a group of Transact-SQL statements compiled into a single execution plan."

Note that stored procedures can include programming logic, such as IF statements.

I don't discuss the benefits of SPs, the when and the why. This is obviously something that you should investigate and understand, starting with the above articles.



Start Microsoft SQL Server Management Studio and create a database. I called my Staff3. (You don't have to use Management Studio, LINQPad and PowerShell are alternatives if you are familiar with either.)
CREATE DATABASE Staff3
GO
USE Staff3
GO


Then create a single table named Staff and populate it with some data:
USE Staff3
GO
CREATE TABLE Staff
    (StaffID int PRIMARY KEY, 
    FirstName varchar(30) NOT NULL, 
    LastName varchar(30) NOT NULL, 
    Salary money, 
    Grade tinyint, 
    OfficeID varchar(5) NOT NULL, 
    DeptID varchar(5), 
    Extn smallint, 
    Bonus bit, 
    BonusRate decimal(4,3), 
    StartDate date, 
    EndDate date, 
);


INSERT INTO Staff
    (StaffID, FirstName, LastName, Salary, Grade, OfficeID, DeptID, Extn, Bonus, BonusRate, StartDate, EndDate)
VALUES
    (101, 'Cole', 'Russell', 22000, 6, 'LDN', 'SAL', 5525, 0, 0.03, '1980-07-21', NULL),
    (104, 'Charlotte', 'Hill', 31500, 4, 'LEE', 'SAL', 3175, 1, 0.04, '1980-12-01', NULL),
    (105, 'Diego', 'Warren', 54000, 1, 'LDN', 'ADM', 5579, 1, 0.05, '1981-03-30', NULL),
    (113, 'Chloe', 'Hall', 22000, 6, 'LDN', 'SAL', 5518, 1, 0.03, '1981-05-05', NULL),
    (114, 'Melanie', 'Cruz', 41000, 2, 'BIR', 'SAL', 4142, 0, 0.05, '1981-07-20', NULL),
    (116, 'David', 'Garcia', 32000, 4, 'BIR', 'ADM', 4128, 1, 0.04, '1981-10-19', NULL),
    (119, 'Nevaeh', 'Pierce', 26000, 5, 'BIR', 'ACC', 4108, 1, 0.03, '1981-11-30', NULL),
    (121, 'Makayla', 'Bryant', 28000, 5, 'LDN', 'SAL', 5540, 1, 0.03, '1982-10-04', '2010-03-31'),
    (122, 'Savannah', 'Lopez', 41000, 2, 'LDN', 'IT', 5550, 0, 0.05, '1983-01-04', NULL),
    (124, 'Landon', 'Watson', 20000, 6, 'BIR', 'ADM', 4139, 1, 0.03, '1983-12-05', NULL),
    (126, 'Carlos', 'Lawrence', 28000, 5, 'LDN', 'SAL', 5537, 1, 0.03, '1984-04-16', NULL),
    (128, 'Grace', 'Watson', 41000, 2, 'BIR', 'ADM', 4119, 1, 0.05, '1984-06-04', NULL),
    (129, 'Brady', 'Rose', 40500, 2, 'BIR', 'ADM', 4123, 1, 0.05, '1984-12-31', NULL),
    (130, 'Anna', 'Rose', 37000, 3, 'BIR', 'ADM', 4125, 1, 0.04, '1985-01-07', NULL),
    (131, 'Trinity', 'Gonzalez', 25500, 5, 'LEE', 'ADM', 3187, 1, 0.03, '1985-04-01', NULL)
GO


At this point I created a C# Console application named 'StoredProcedure'. All I want to do with it is to prove that I can connect to the database and read 'something' from the Staff table. I did use using statements though, making it easier to extend later on.
using System;
using System.Data;
using System.Data.SqlClient;

namespace StoredProcedure {
    class Program {
        static void Main(string[] args) {
            string sConn = "Server=ANDREW-PC\\SQLEXPRESS;Database=Staff3;Trusted_Connection=Yes";
            using (SqlConnection conn = new SqlConnection(sConn)) {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM Staff", conn)) {
                    int rows = (int)cmd.ExecuteScalar();
                    Console.WriteLine(rows); // should be 15
                }
            }

            Console.ReadKey();
        }
    }
}


SQL Server connection strings

Once you've got this working you can close Visual Studio and return to Management Studio.

Start by creating and executing a very simple procedure, without any parameters or return value:
CREATE PROCEDURE OfficeStats
AS
SELECT FirstName, LastName, OfficeID FROM Staff 
WHERE OfficeID = 'LDN'
GO

EXEC OfficeStats


The syntax is here, CREATE PROCEDURE (Transact-SQL) (msdn). Notice that I have omitted the optional BEGIN and END keywords. Typically, an SP is created separately from its testing and execution, so BEGIN and END aren't too significant. Use them if you prefer. If you are creating and testing in the same query make sure to follow the definition with GO.

You could name your SPs with a prefix such as spProcedureName, but don't use sp_ as this is used by MS for system SPs.

You can use EXEC or EXECUTE (and PROC or PROCEDURE).

If you are creating and testing in the same window then this provides a basic outline for replacing the SP:
DROP PROCEDURE OfficeStats
GO
CREATE PROCEDURE OfficeStats 
AS


GO




You may want to investigate checking if the SP exists before dropping it. This SO topic discusses checking if a table exists with code like IF OBJECT_ID('*objectName*', 'U') IS NOT NULL. I suspect that similar code will work for an SP but I didn't pursue this, although it looks like 'P' replaces 'U' for a stored procedure, sys.objects (Transact-SQL).

(The deletion and re-creation is only appropriate during development, an SP will most often be a persistent object in the database.)



I prefer to put the AS keyword on a separate line, as I commonly forget to include it if at the end of the first line.

Run the above code and it should display 6 rows for the London (LDN) staff. We aren't using parameters and the result of executing the SP is the output from the first SELECT statement. (If there is more than one SELECT then executing an SP in Management Studio will display both resultsets.)

Now we can add an input and an output parameter. We will input an OfficeID code ('LDN') and obtain the list of this office's staff, also outputting the average of their salaries.
DROP PROCEDURE OfficeStats
GO
CREATE PROCEDURE OfficeStats @Office varchar(5), @Average money OUTPUT
AS
SELECT FirstName, LastName, OfficeID FROM Staff
WHERE OfficeID = @Office

SELECT @Average = Avg(Salary) FROM Staff WHERE OfficeID = @Office
GO


The input parameter is a code (e.g. 'LDN') for the OfficeID which is used in the WHERE criteria.

OUTPUT or OUT identify output parameters. SELECT @parameter = .. is assigning the parameter a value.

You could put the parameters on separate lines (after CREATE PROCEDURE, and before AS) if you prefer.

We can test our procedure in Management Studio, declaring a variable to store the output parameter's value:
DECLARE @OfficeAverage money

EXEC OfficeStats @Office = 'LDN', @Average = @OfficeAverage OUTPUT
SELECT [The Average] = @OfficeAverage


We can make a slight addition to include a RETURN value, the number of staff for the chosen office. RETURN (Transact-SQL)
DROP PROCEDURE OfficeStats
GO
CREATE PROCEDURE OfficeStats @Office varchar(5), @Average money OUTPUT
AS
SELECT FirstName, LastName, OfficeID FROM Staff
WHERE OfficeID = @Office;

SELECT @Average = Avg(Salary) FROM Staff WHERE OfficeID = @Office

RETURN (SELECT Count(*) FROM Staff WHERE OfficeID = @Office)
GO


RETURN causes an immediate return/exit from the stored procedure (with or without a value).

The return value is an integer and its typical use is for an error or status code. There is also only one return value. For anything else, or for more than one value, we would use output parameters.

Testing our new version in Management Studio:
DECLARE @OfficeAverage money
DECLARE @OfficeCount int

EXEC @OfficeCount = OfficeStats @Office = 'LDN', @Average = @OfficeAverage OUTPUT

PRINT 'Average Salary for ''LDN'' is ' + CONVERT(char(10), @OfficeAverage)
PRINT 'Number of ''LDN'' staff is ' + CONVERT(char(4), @OfficeCount)


In the Messages pane:

Quote

(6 row(s) affected)
Average Salary for 'LDN' is 32500.00
Number of 'LDN' staff is 6

Now we can return to our Console application and modify to the following.
using System;
using System.Data;
using System.Data.SqlClient;

namespace StoredProcedure {
    class Program {
        static void Main(string[] args) {
            string sConn = "Server=ANDREW-PC\\SQLEXPRESS;Database=Staff3;Trusted_Connection=Yes";
            using (SqlConnection conn = new SqlConnection(sConn)) {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("OfficeStats", conn)) {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@Office", "LDN");  // input parameter

                    // output parameter
                    SqlParameter average = new SqlParameter("@Average", DbType.Currency);
                    average.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(average);

                    // return value
                    SqlParameter count = new SqlParameter("@Returned", DbType.Int32);
                    count.Direction = System.Data.ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(count);

                    using (SqlDataReader reader = cmd.ExecuteReader(Commandbehavior.CloseConnection)) {
                        while (reader.Read()) {
                            string firstName = reader.GetString(reader.GetOrdinal("FirstName"));
                            string lastName = reader.GetString(reader.GetOrdinal("LastName"));
                            string office = reader.GetString(reader.GetOrdinal("OfficeID"));

                            Console.WriteLine("{0} {1} {2}", firstName, lastName, office);
                        }
                    }
                    Console.WriteLine("Average 'LDN' Salary {0:c}", average.Value);
                    Console.WriteLine("Number of 'LDN' staff, {0}", count.Value);
                }
            }

            Console.ReadKey();
        }
    }
}


Quote

Cole Russell LDN
Diego Warren LDN
Chloe Hall LDN
Makayla Bryant LDN
Savannah Lopez LDN
Carlos Lawrence LDN
Average 'LDN' Salary 32,500.00
Number of 'LDN' staff, 6

Study this code closely as it contains a lot of information. Refer to the MSDN documentation for anything you are unfamiliar with.

Notice in particular the use of using statements to dispose of resources, and that for input parameters (the default) I just use AddWithValue(), letting the database handle its type.

Covering similar ground:

Stored Procedures: Returning Data

Is This A Good Question/Topic? 0
  • +

Replies To: Stored Procedure (Input, Output, Return Value)

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Posted 01 February 2016 - 09:01 PM

Shouldn't this be classified under MS Sql Server, not C#?
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is offline

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,562
  • Joined: 12-December 12

Posted 02 February 2016 - 12:34 PM

Yes, my original intention was to add it to the Databases tutorials. It was my inclusion of the C# code that pushed me to put it here instead.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1