CODEWITHSUNDEEP
sql-servervb.netstored-procedures
MrBill
MrBill

Reputation: 100

Return a single record from a SQL Server Stored Procedure, or use output parameters

I am sending an order number, which is unique, to SQL Server from VB.NET by way of a stored procedure. The procedure looks up the corresponding record in the table. I wish to return the values in the selected fields of that record to VB.NET. I can do this using output parameters, which means I have to define them in the procedure and then again in VB, or I can return that single record and put it in a datatable, then extract the values, avoiding the typing. Is there a reason I should stick with output parameters?

Upvotes: 0

Views: 2010

Answers (2)

Moka Naga Raju
Moka Naga Raju

Reputation: 56

Stored Procedure without output parameter,

We can get a result as a table format(Rows and columns.) It can be an one or more records or columns.

But Stored Procedure with output parameter:

It returns single string.

This is the matter of whether you get single strings or multiple records.

For good programming structure if you get only single output, mapping with the vb.net is a good practice. For more than one record extract the values.

Upvotes: 0

Steve
Steve

Reputation: 216302

In your simple scenario I don't think you need to use output parameters. Also, if your return value is just one single record resulting from your stored procedure last SELECT statement, then you don't need neither the infrastructure of a DataTable (more than one record)

Dim cmd = New SqlCommand(procName, connection)
cmd.CommandType = CommandType.StoredProcedure
Dim reader = cmd.ExecuteReader()
While reader.Read()
    TextBox1.Text = reader(0) ' First field from the internal SELECT of the stored procedure
    .... 'other fields 
End While

Upvotes: 3

Related Questions