Visual Studio 2005 Cookbook – Database, Using Stored Procedures

Recipe 13.5. Using Stored Procedures

Problem

You need to use a stored procedure in your database, and you’re not sure how to specify values for its input and output parameters.

Solution

Use the command object’s Parameters collection to set and retrieve stored procedure argument values.

Discussion

Here’s a simple SQL Server stored procedure that does nothing more than retrieve a field from a table given its ID value:

	CREATE PROCEDURE GetRecordName
	   @PriKey int,
	   @NameResult varchar(50) OUT
	AS
	BEGIN
	   -- Given an ID value, return the RecordName field.
	   SET @NameResult =
	      (SELECT RecordName FROM Table1 WHERE ID = @PriKey);
	END

To use this stored procedure, create a command object that calls it, and add separate input and output parameters: Read more…