Stored procedures are what makes SQL Server such a robust and scalable database management system. They are the most efficient way to manipulate data using a complex batch of TSQL code.

In just a minute we're going to compare how we call stored procedures using both ADO/ASP and ADO.NET/ASP.NET, but first, run the following TSQL code using query analyzer to create a new stored procedure in the pubs database:

USE PUBS
GO

CREATE PROC sp_AddJob
(
@jobDesc VARCHAR(50),
@minLevel TINYINT,
@maxLevel TINYINT
)
AS

-- Add a record to the jobs table
-- and return no data

SET NOCOUNT ON
INSERT INTO jobs(job_desc, min_lvl, max_lvl)
VALUES(@jobDesc, @minLevel, @maxLevel)

The stored procedure we've just created is called sp_AddJob, it accepts three parameters, and will insert a new record into the jobs table of the pubs database.

Using an ADO command object and parameters, here's how we would call it using ASP:

<%

const adVarChar = 200
const adTinyInt = 16
const adParamInput = 1

dim objConn
dim objComm
dim paramDesc
dim paramMinLvl
dim paramMaxLvl

set objConn = Server.CreateObject("ADODB.Connection")
set objComm = Server.CreateObject("ADODB.Command")

objConn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=Pubs; UId=sa; Pwd="

objComm.ActiveConnection = objConn
objComm.CommandType = 4 'adCmdStoredProc
objComm.CommandText = "sp_AddJob"

set paramDesc = objComm.CreateParameter("jobDesc", adVarChar, adParamInput, 50, "Test Job")
set paramMinLvl = objComm.CreateParameter("minLevel", adTinyInt, adParamInput, , 10)
set paramMaxLvl = objComm.CreateParameter("maxLevel", adTinyInt, adParamInput, , 100)

objComm.Parameters.Append(paramDesc)
objComm.Parameters.Append(paramMinLvl)
objComm.Parameters.Append(paramMaxLvl)

objComm.Execute

%>

As you can see, I've created three ADO parameter objects using the command objects CreateParameter() function. All are set as input parameters, and the Execute() function actually runs the stored procedure.

Similarly, to achieve the same functionality using ADO.NET we need to instantiate an SqlCommand or OleDbCommand class, set its CommandType property to CommandType.StoredProcedure, and create three parameters, like this:

<%@ import namespace="System.Data" %>
<%@ import namespace="System.Data.SqlClient" %>

<script language="c#" runat="server">

public void Page_Load(object sender, EventArgs e)
{
SqlConnection objConn = new SqlConnection("Server=(local); Database=Pubs; UId=sa; Pwd=");
objConn.Open();

SqlCommand objComm = new SqlCommand("sp_AddJob", objConn);
objComm.CommandType = CommandType.StoredProcedure;

objComm.Parameters.Add(new SqlParameter("@jobDesc", SqlDbType.VarChar, 50));

objComm.Parameters.Add(new SqlParameter("@minLevel", SqlDbType.TinyInt, 0));

objComm.Parameters.Add(new SqlParameter("@maxLevel", SqlDbType.TinyInt, 0));

objComm.Parameters[0].Value = "Test Job";
objComm.Parameters[1].Value = 10;
objComm.Parameters[2].Value = 100;

objComm.ExecuteNonQuery();
objComm.UpdatedRowSource = UpdateRowSource.None;

}

</script>

In the example above, we create a new SqlCommand object, passing in "sp_AddJob" (which is the name of the stored procedure we want to execute) as the first argument to its constructor.

We call the "Add" function of our SqlCommand's Parameters collection to add three new parameters. Once the parameters have been added, we still have to set their values. The Parameters collection of our SqlCommand object exposes a public indexer which we use the set the values of the parameters explicitly. Instead of referencing the parameters by their index, we could specify use their name, such as:

objComm.Parameters["@jobDesc"].Value = "Test Job";

Also notice that each parameter must be prepended with the @ symbol. The last line of our Page_Load() function is:

objComm.UpdatedRowSource = UpdateRowSource.None;

The UpdatedRowSource property of our SqlCommand object is used to specify how query command results are applied to the row(s) being updated. It has five possible values which are Both, FirstReturnedRecord, None, OutputParameters, and value__. We specify none, because our query returns no results and therefore no data updates are required.

It's great being able to execute a stored procedure with no return values, but what about when we want to return output parameters, or even entire rowsets? Run the following code in query analyzer. It creates a new stored procedure in the pubs table called sp_GetBookCosts, which returns the sum of all price fields in the titles table that have a specific royalty field value:

USE PUBS
GO

CREATE PROC sp_GetBookCosts
(
@royalty INT,
@priceTotal INT OUTPUT
)
AS

-- Get the sum of all books
-- whose royalty field matches @royalty

SET NOCOUNT ON
SET @priceTotal = (SELECT SUM(price) FROM titles WHERE royalty = @royalty)

To get all books from the titles table whose royalty field is equal to 10, we would use this ASP/ADO code:

objComm.CommandType = 4 'adCmdStoredProc
objComm.CommandText = "sp_GetBookCosts"

set paramRoyalty = objComm.CreateParameter("royalty", adInteger, adParamInput, , 10)

set paramPrice = objComm.CreateParameter("priceTotal", adInteger, adParamOutput)

objComm.Parameters.Append(paramRoyalty)
objComm.Parameters.Append(paramPrice)

objComm.Execute
totalPrice = objComm.Parameters("priceTotal").value

Response.Write "Total is " & totalPrice

Just like in the ASP/ADO example above, we must specify our parameters as output parameters in ADO.NET. We must also set the UpdatedRowSource value of our SqlCommand object to UpdateRowSource.OutputParameters, which tells ADO.NET to provide us with a value for our output parameter:

SqlCommand objComm = new SqlCommand("sp_GetBookCosts", objConn);
objComm.CommandType = CommandType.StoredProcedure;

objComm.Parameters.Add(new SqlParameter("@royalty", SqlDbType.Int));

objComm.Parameters.Add(new SqlParameter("@priceTotal", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, "priceTotal", DataRowVersion.Default, null));

objComm.Parameters["@royalty"].Value = 10;
objComm.ExecuteNonQuery();
objComm.UpdatedRowSource = UpdateRowSource.OutputParameters;

int totalPrice = (int)objComm.Parameters["@priceTotal"].Value;
Response.Write("Total is " + totalPrice);

The only thing new about our example above is that we've called a different constructor to add a new parameter to our SqlCommand object, setting the parameter direction to ParameterDirection.Output.

	1 2 3 4 5 6 7