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

  • 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.

    • Moved by Val MazurModerator Tuesday, May 8, 2012 7:08 PM (From:ADO.NET Entity Framework and LINQ to Entities)
    Wednesday, May 2, 2012 9:28 AM

Answers

  • 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

    Tuesday, May 8, 2012 4:15 AM
    Moderator

All replies

  • 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!

    Thursday, May 3, 2012 8:06 AM
  • 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.

    Friday, May 4, 2012 12:41 PM
  • 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!

    Monday, May 7, 2012 6:36 AM
  • 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.

    Monday, May 7, 2012 8:09 AM
  • Hi Nietzsche61,

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


    Go go Doraemon!

    Monday, May 7, 2012 8:19 AM
  • 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!

    Monday, May 7, 2012 8:29 AM
  • 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.

    Monday, May 7, 2012 8:42 AM
  • Please look at the case in stackoverflow which I posted, it used ExecuteNonQuery to return the output parameter's value.

    Go go Doraemon!

    Monday, May 7, 2012 8:48 AM
  • 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.

    Monday, May 7, 2012 9:17 AM
  • 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

    Monday, May 7, 2012 3:00 PM
    Moderator
  • 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.

    Tuesday, May 8, 2012 1:58 AM
  • 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

    Tuesday, May 8, 2012 4:15 AM
    Moderator
  • 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.

    Tuesday, May 8, 2012 4:50 AM