ADO .Net and stored procedure Output parameters in Insert/Update/Delete operations

Question

Hi,

I'm using .Net 4.0 and SQL 2008 R2

Having read quite a lot of documentation about returning data from stored procedures in ADO .Net, I wanted to know if there were any best practices regarding how to return data from stored procedures in ADO .Net that are Insert/Update or Delete operations. The number of parameters returned are not important, but can be a new Id, a rowversion or a date column.

I can use 2 methods:

• one involves using an OUTPUT clause in the stored procedure and an ExecuteReader in ADO .Net
• another involves an additional SELECT in the stored procedure to set OUTPUT parameters and to use ExecuteNonQuery in ADO .Net

Which is the best solution ?

I've read that the OUTPUT clause is a good solution in SQL, but I couldn't get it to work with ExecuteNonQuery.
I've also read that ExecuteReader was designed for fetching data and ExecuteNonQuery for Insert/Update or Delete operations (MSDN).

Thank you for your inputs.

---

Alex.

Answer 1

Yes, as you have seen in MSDN, we often use ExecuteReader to fetch data, and use ExecuteNonQuery for insert/update and delete operation. If using ExecuteReader to return output parameter, the output parameters will not be fetched before the reader closing or the reader read to the end.

---

Go go Doraemon!

Answer 2

Thanks,

So if ExecuteNonQuery is the way to go for Insert/Update and Delete operations, is there a way to get the values from an OUTPUT clause in a stored procedure ?

INSERT INTO [dbo].[Table]
	(
		[Field1],
		[Field2],
		[Field3]
	)

	OUTPUT
	inserted.Field1
	inserted.Field2
	VALUES
	(
		@Field1,
		@Field2,
		@Field3
	)

Is it possible to get the inserted values from the OUTPUT clause from ADO .Net with an ExecuteNonQuery ?

Thanks.

---

Alex.

Answer 3

Yes, ExecuteNonQuery method also can help you to retrieve the output parameter, you can refer to the link below.

http://stackoverflow.com/questions/290652/get-output-parameter-value-in-ado-net

---

Go go Doraemon!

Answer 4

Hi,

Thanks. I've read this link before, it doesn't explain how to get output parameters from an OUTPUT clause.
I know how to get OUTPUT parameters from a stored procedure with ExecuteNonQuery, I just don't know how to set them within an OUTPUT clause (as I mentioned in my first post)...

I would like to do something like :

OUTPUT
   SET @field1 = inserted.Field1,
   SET @field2 = inserted.Field2

(with @field1 and @field2 being stored procedure Output parameters)

But it doesn't work...

So is there a way to set stored procedure Output parameters from within an OUTPUT clause, or is an additional SELECT required?

---

Alex.

Answer 5

Hi Nietzsche61,

Yes, you need to write select query for output parameters.

---

Go go Doraemon!

Answer 6

Hi, if you want to use executereader, the output parameters will unavailable before closing the reader, so I think the second solution is better. Furthermore, this link may help.

http://msdn.microsoft.com/en-us/library/ms971497

---

Go go Doraemon!

Answer 7

Ok, so does it mean that there is no way of getting the returned values from an OUTPUT clause in a stored procedure with an ExecuteNonQuery in ADO .Net ?

I don't want to use ExecuteReader, I want to use ExecuteNonQuery, but I would like to use it with an OUTPUT clause in my stored procedure if it is possible...
What I said in my first post is : I can get the OUTPUT clause returned values, but with an ExecuteReader. I couldn't find a way to get them with an ExecuteNonQuery. Is it possible ?

---

Alex.

Answer 8

Please look at the case in stackoverflow which I posted, it used ExecuteNonQuery to return the output parameter's value.

---

Go go Doraemon!

Answer 9

Thanks Dorado999,

Ok, I think you don't understand my question because you don't see the difference between Output parameters in Stored procedure and Output clause (in T-SQL). http://msdn.microsoft.com/en-us/library/ms177564(v=sql.105).aspx

Given that TVP (Table value Parameters) in Stored procedure have to be readonly, I don't see a way to return the values of the Output Clause in a TVP (with the OUTPUT <dml_select_list> INTO { @table_variable | output_table } clause ). (http://msdn.microsoft.com/en-us/library/bb675163.aspx)

I can get the returned value from an Output Clause (in a stored procedure) with an ExecuteReader but not with an ExecuteNonQuery.

Again, is it possible?

I'm sorry, but I cannot find the answer to my question in the stackoverflow link you gave me, or maybe it is obvious and I just don't see it...

As you said before : "Yes, you need to write select query for output parameters." So is it possible then to do a SELECT in an OUTPUT Clause ?

Thanks again,

---

Alex.

Answer 10

Hi Nietzsche61,

Welcome to MSDN Forum.

ExecuteNonQuery only can return the affected number of records when you do delete/update or insert operation, certainly, it also can return the output parameters. But Output Clause can't be returned by ExecuteNonQuery method, Output Clause likes a select statement, it return information affected by your insert/update or delete command, so you need to use ExecuteReader to do that.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 11

Thank you Allen.

So As I suspected, it is not possible to return Output clause values with ExecuteNonQuery.

Could you please confirm that the best practice to return parameters from an insert/update/delete stored procedure is the 2nd option I proposed in my first post ?
i.e. :
Add an additional select in the stored procedure :

CREATE PROCEDURE [dbo].[Sp_Table_Insert]
	@Field1 type = NULL OUTPUT,
	@Field2 type= NULL OUTPUT,
	@Field3 type = NULL,
	@Field4 type = NULL
AS
	BEGIN
	EXEC [dbo].[Sp_GetId] @Id = @Field1 OUTPUT
	INSERT INTO [dbo].[Table]
	(
		[Field1],
		[Field2],
		[Field3],
		[Field4]
	)
	VALUES
	(
		@Field1,
		GETDATE(),
		@Field3,
		@Field4
	)
SELECT @Field1 = [Field1], @Field2 = [Field2] FROM [dbo].[Table] WHERE [Field1] = @Field1

and use an ExecuteNonQuery for this stored procedure in ADO .Net ?

The other option (Output Clause + ExecuteReader in stored proc) is not the way to go.

Thanks,

---

Alex.

Answer 12

Hi Nietzsche61,

Yes. When we want to retrieve data from the just inserted/updated or deleted record, output parameters with an additional select query is the common way. If you only want to retrieve the identity value, such as id from the record which just inserted, "SCOPE_IDENTITY" is also an option, here's a FAQ I think may help you, please refer here.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 13

Thanks Allen,

I know about SCOPE_IDENTITY, but in my case, I need to return more values, like I said in my first post, not only Id but also rowversion (for concurrency issues), inserted/updated date, ...

So Select with output parameters in stored procedure + ExecuteNonQuery in ADO .Net is the way to go.

---

Alex.