ADO.NET and the OUTPUT Clause

  • Thread starter Thread starter Mike Mertes
  • Start date Start date
M

Mike Mertes

Hi all,

What's the easiest way to retrieve a single value (post insert identity
fetch) from an output clause in a stored procedure?

For instance:

@CREATE PROCEDURE sp_Insert
@Var1 int
@Var2 int
AS
INSERT INTO tbl1 (Fld1, Fld2)
OUTPUT INSERTED.RecordID
VALUES (@Var1, @Var2);

I am currently using SqlCommand.ExecuteNonQuery to return rows affected
so I prefer not to return a temp table to a dataset or reader. Can I
insert the value into an OUTPUT param?

TIA

-Mike
 
Hi all,

What's the easiest way to retrieve a single value (post insert identity
fetch) from an output clause in a stored procedure?

For instance:

@CREATE PROCEDURE sp_Insert
@Var1 int
@Var2 int
AS
INSERT INTO tbl1 (Fld1, Fld2)
OUTPUT INSERTED.RecordID
VALUES (@Var1, @Var2);

I am currently using SqlCommand.ExecuteNonQuery to return rows affected
so I prefer not to return a temp table to a dataset or reader. Can I
insert the value into an OUTPUT param?

TIA

-Mike
Click to expand...

Modify your procedure like so:

@CREATE PROCEDURE sp_Insert
@Var1 int,
@Var2 int,
@NewID int output
AS
INSERT INTO tbl1 (Fld1, Fld2)
VALUES (@Var1, @Var2);
select @NewID = ident_current('tbl1')

Then alter your command like so:

//
// Create a parameter object
//
SqlParameter NewID = new SqlParameter("@CurrencyID",SqlDbType.Int);
//
// Set the parameter direction
//
NewID.Direction=ParameterDirection.Output;
//
// Add the parameter to the command
//
cmd.Parameters.Add(NewID);
//
// Add the other 2 parameters,var1 and var2
//
....
....
....
//
// Execute the query
//
cmd.ExecuteNonQuery();
//
// Retrieve the value
//
ID=(int)NewID.Value;
 
Mike,

This is an AdoNet newsgroup not a SQL transact code newsgroup. However to
get a single value in AdoNet you use the command.ExecuteScalar

Cor
 
Mike,

This is an AdoNet newsgroup not a SQL transact code newsgroup. However
to get a single value in AdoNet you use the command.ExecuteScalar

Cor
Click to expand...

That's funny, I thought I was asking a specifically ADO.NET oriented
question. I know how to return values from T-SQL using the OUTPUT clause. I
*was not* asking for advice on T-SQL. I *was* asking about what methods or
objects I can use to retrieve that value from the stored procedure
programmatically. (BTW, I am already using cmd.ExecuteScalar to return the
rows affected by the INSERT.)

Another poster played along with my last suggestion and retrieved the value
by adding an additional SELECT and an output parameter (yes, T-SQL... the
focus, however, is on ADO). But, I was looking for something that uses the
OUTPUT clause specifically (the second select may incur a small performance
hit and concurrency issues. Read other reply if you care.).

Point is; If you actually want to give useful advice you could atleast
suggest that my T-SQL needs modification before I can employ cooperating
code. Telling me to GTFO because you saw T-SQL code in my post, on the
other hand, is just plain offensive.
 
Modify your procedure like so:

@CREATE PROCEDURE sp_Insert
@Var1 int,
@Var2 int,
@NewID int output
AS
INSERT INTO tbl1 (Fld1, Fld2)
VALUES (@Var1, @Var2);
select @NewID = ident_current('tbl1')

Then alter your command like so:

//
// Create a parameter object
//
SqlParameter NewID = new SqlParameter("@CurrencyID",SqlDbType.Int);
//
// Set the parameter direction
//
NewID.Direction=ParameterDirection.Output;
//
// Add the parameter to the command
//
cmd.Parameters.Add(NewID);
//
// Add the other 2 parameters,var1 and var2
//
...
...
...
//
// Execute the query
//
cmd.ExecuteNonQuery();
//
// Retrieve the value
//
ID=(int)NewID.Value;
Click to expand...

Thank you for the suggestion. It does work but as I understand it that's an
outdated method, a crock, if you will. The successive SELECT incurs a small
performance hit (2 operations on the server instead of one bundled
operation), but more importantly it raises concurrency issues.
"ident_current('tbl1')" returns the current/last identity, which could
theoretically be different than the identity inserted by this same stored
procedure at some previous yet undefined point in time.

Don't get me wrong, I'm probably nit picking, but it's nice to know that
there are different ways to accomplish a task and to know why one choice
might be better than another in a certain situation.

The MSDN documentation was very clear about the usage of OUTPUT, and it was
easy to find articles on why they added it to T-SQL. Oppositely, I've found
that it's NOT easy to find guidelines on how to retrieve OUTPUT values
using managed code without sacrificing some functionality somewhere.

Hope that now it's clear why I'm asking for advice from ADO.NET coders. :)

-Mike
 
Another poster played along with my last suggestion and retrieved the value
by adding an additional SELECT and an output parameter (yes, T-SQL... the
focus, however, is on ADO). But, I was looking for something that uses the
OUTPUT clause specifically (the second select may incur a small performance
hit and concurrency issues. Read other reply if you care.).
Click to expand...

I did use the output clause in the procedure. Or what did you mean?

As for the use of select, you could use the set statement instead of the
select.

And as for the concurrency issue, you could wrap the procedure in a
transaction or use a SqlTransaction object for the command
 
I did use the output clause in the procedure. Or what did you mean?

As for the use of select, you could use the set statement instead of
the select.

And as for the concurrency issue, you could wrap the procedure in a
transaction or use a SqlTransaction object for the command
Click to expand...

http://msdn2.microsoft.com/en-us/library/ms177564.aspx

ala:
@CREATE PROCEDURE sp_Insert
@Var1 int
@Var2 int
AS
INSERT INTO tbl1 (Fld1, Fld2)
OUTPUT INSERTED.RecordID
VALUES (@Var1, @Var2);

I understand it's new 2k5 functionality and I want to make this a common
practice for myself because it's clean and fast. If you view it in the
query analyzer it is not two separate queries (an update and then a
succeeding select), nor is it even two trips to the database.

Wrapping in a transaction might be the cleanest alternative solution... and
I really don't know why I hadn't thought of it. :)
 
ala:
@CREATE PROCEDURE sp_Insert
@Var1 int
@Var2 int
AS
INSERT INTO tbl1 (Fld1, Fld2)
OUTPUT INSERTED.RecordID
VALUES (@Var1, @Var2);

I understand it's new 2k5 functionality and I want to make this a common
practice for myself because it's clean and fast. If you view it in the
query analyzer it is not two separate queries (an update and then a
succeeding select), nor is it even two trips to the database.

Wrapping in a transaction might be the cleanest alternative solution... and
I really don't know why I hadn't thought of it. :)
Click to expand...

Nice! Well, you live you learn! I can chalk that up as something i didn't
know before
 
Nice! Well, you live you learn! I can chalk that up as something i
didn't know before
Click to expand...

That's how I felt when I learned about it a tad bit ago :) Also thanks for
your help again, your influence led me to discovering the Scope_identity()
function, which returns the last new identity from the current session
(averting concurrency issues) rather than the last identity from the
specified table as you suggested in your example. :)

I'll update this thread when I discover an elegant way of putting the
OUTPUT values into adonet params or something else less hackish.

/me wonders if anyone else has suggestions ?
 
Here's an idea.

How to get a parameter back:

Dim ss = "SELECT @UnitPrice = UnitPrice, @UnitsInStock = UnitsInStock, "
& _
"FROM Products WHERE ProductName = @ProductName"

Dim pUnitPrice, pInStock, pProductName As SqlParameter
pUnitPrice = cmd.Parameters.Add("@UnitPrice", SqlDbType.Money)
pUnitPrice.Direction = ParameterDirection.Output
pInStock = cmd.Parameters.Add("@UnitsInStock", SqlDbType.NVarChar, 20)
pInStock.Direction = ParameterDirection.Output
pProductName = cmd.Parameters.Add("ProductName", SqlDbType.NvarChar, 40)
pProductName.Value = "Chai"

cmd.ExecuteNonQuery()
if pUnitPrice.Value Is DBNull.Value Then 'none were found
Console.WriteLine("No product found named {0}", pProductName.Value)
Else
Console.WriteLine("Unit price: {0}", pUnitPrice.Value)
Console.WriteLine("In Stock: {0}", pInStock.Value)
End If

Robin S.
------------------------
 
Stephany,
And what results did you get when you executed that code, if indeed it
compiled?
Click to expand...

Why don't you try it, to quote some messages I saw from somebody else?

:-)

Cor
 
I'm attempting to get RobinS to think about what he posted.

And it's dangerous to quote out of context.
 
Here it is with the connection and cmd objects added.

Dim ss As String = "SELECT @UnitPrice = UnitPrice, " & _
" @UnitsInStock = UnitsInStock " & _
"FROM Products WHERE ProductName = @ProductName"

Dim cn As New SqlConnection(My.Settings.NorthwindConnectionString)
cn.Open()
Dim cmd As New SqlCommand(ss, cn)

Dim pUnitPrice, pInStock, pProductName As SqlParameter
pUnitPrice = cmd.Parameters.Add("@UnitPrice", SqlDbType.Money)
pUnitPrice.Direction = ParameterDirection.Output
pInStock = cmd.Parameters.Add("@UnitsInStock", SqlDbType.NVarChar, 20)
pInStock.Direction = ParameterDirection.Output
pProductName = cmd.Parameters.Add("ProductName", SqlDbType.NVarChar, 40)
pProductName.Value = "Chai"

cmd.ExecuteNonQuery()

If pUnitPrice.Value Is DBNull.Value Then 'none were found
Console.WriteLine("No product found named {0}", pProductName.Value)
Else
Console.WriteLine("Unit price: {0}", pUnitPrice.Value)
Console.WriteLine("In Stock: {0}", pInStock.Value)
End If
cn.Close()


Robin S.
------------------------------------------
 
Did you have a point? Or did you just really want
to know the quantity and price of Chai in the Northwinds
Products table?

Just wondering,
Robin S.
----------------------------------------
 
I was going to follow-up anyway but now that you have reminded me ...

At face value I was a little dubious that it would actually work.

I have certainly used output parameters before but only in the context of
stored procedures, where, of course, such parameters are explicitly declared
in the stored procedure's parameter list or as the implicit RETURN_VALUE
parameter.

I had never seen them used in the context of local variables in a single SQL
statement before. It took a little bit to thinking to get my head around it.

I can see that it could certainly useful as an alternative to using
ExecuteReader() when one wants to retrieve multiple values from a single
row.

I stand educated.
 
Phew. That's a relief. I was afraid you were going to come
back and say my methodology would cause SQLServer to go
belly up or something. My knowledge isn't as wide or as
deep as yours, and I try really hard not to post something
unless I am pretty certain it will work, and in most cases
I try it out first just to make sure.

Thanks,
Robin S.
-------------------------------------
 
Back
Top