Call a Parameterized Stored Procedure in SQL Server 2000

by William Ryan

Unless you live under a rock, you have heard about Stored Procedures.  These days, they are more important than ever.  Stated simply, Stored Procs provide the following benefits:

1)  Performance - the RDBMS can execute Stored Procs much faster than standard SQL Statements.
2)  Security - You can restrict a User's access to the execution of Stored Procedures that you write.  You can then grant the user Execute Permission to the procedures and grant SELECT/DELETE/INSERT permissions to the procedure.  If you are in an environment where your database is accessible from outside your trused domain, this could be the difference between a successful career in IT and flipping burgers.
3)  Simplicity - Stored procs encapsulate your logic and allow modifications to be made which are transparent to the end user.
4)There's simply no downside to them and no good reason not to.

Ok, great, so how do I use them.

Examine the code snippet below:

Try        _cn = New SqlConnection(csPublic)        _cmd = New SqlCommand("usp_TrackUser", _cn)        _cmd.CommandType = CommandType.StoredProcedure        _cmd.Parameters.Clear()        _cmd.Parameters.Add("@UserName", uid)        _cmd.Parameters.Add("@status", stat)        _cmd.Parameters.Add("@HostName", HostName)                  If _cn.State <> ConnectionState.Open Then _cn.Open()         _cmd.ExecuteNonQuery()          Return 1         Catch ex As System.Exception             Return 0         Finally           If _cn.State = ConnectionState.Closed Then _cn.Close() End Try

So what does this do?  Very simple.  I declare a SQLConnection and initialize it with a string constant csPublic.  Then instantiate a SQLCommand giving it the name of the procedure and the connection to use.  I set the CommandType of the Commmand to StoredProcedure, Clear the Parameters collection (in this instance, it's not necessary but it's just a neurotic habit of mine), add the parameters (I was lazy here.... I should have used the overloaded constructor and specified Parameter size and Type which correspond the actual parameter type in the proc), Check if the connection is open and if not, open it (once again, not necessary here and that line should be wrapped in its own try/catch since a failure from the Database opening is much different from the proc failing), then call executeNonQuery.  This is just a way I chose to do it, but ExecuteReader, ExecuteScalar ... would all work the exact same way.