asked on

Do stored procedures need output variables?

I'm just learning about stored procedures. If I don't specify output variables, I can still get all of the data. (i.e. in a Gridview control). But do I really need output variables? Can I still access the individual column data without output variables?

Example below (includes one output variable for row count only):

CREATE PROCEDURE [dbo].[spMyStoredProcedure]
@RowCount int output

AS
BEGIN
	SELECT * FROM dbTable
	select @RowCount=@@ROWCOUNT
END

Open in new window

Jim Horn

No. They can use output parameters, but in most cases they don't and will return a set of data such as your SELECT * FROM dbTable.

intoxicated_curveball

ASKER

I meant to say output parameter (not variable). If I don't set a parameter, how can I access a column (for example in VB.NET C#)?

Vitor Montalvão

You will only need the output parameter if you want the stored procedure to return more than one value.
The main value is returned by the SP itself:

SET @MyVariable = sp_spname()

Open in new window

intoxicated_curveball

ASKER

Vitor: In the example I posted above I'm retrieving the enter table and all columns and rows (in addition to the RowCount parameter). So that is not just "one value", can you clarify your response? However I'm not sure I can access the individual columns with this approach. Here is my ASP.NET code:

SqlConnection MyConnection = new SqlConnection("server=myserver;database=mydatabase;Integrated Security=SSPI;pooling=true");
SqlDataAdapter MyDataAdapter = new SqlDataAdapter("spMyStoredProcedure", MyConnection);
 MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int, 4));
MyDataAdapter.SelectCommand.Parameters["@RowCount"].Direction = ParameterDirection.Output;

DataSet DS = new DataSet();
MyDataAdapter.Fill(DS, "spMyStoredProcedure");
lblRows.Text = MyDataAdapter.SelectCommand.Parameters[0].Value + " Rows Found!";

GrdData.DataSource = DS.Tables["spMyStoredProcedure"].DefaultView;
GrdData.DataBind();

MyDataAdapter.Dispose(); 
MyConnection.Close();

Open in new window

intoxicated_curveball

ASKER

I feel like the following has answered my question as it is returning a 'result set', however I will await for additional comment for answering my own question.

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-2017

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial