Questions about generated stored procedures by VS 2005 (Ta..

First off, recognize that Visual Studio, normally, IS NOT creating Stored
Procedures. It is creating 'inline' SQL code that will be submitted for the
appropriate datadapapter command. It will often be more efficient to create
stored procedures and use them instead of inline code.

Your questions:

1. The SELECT after a UPDATE or INSERT or DELETE is used to refresh your
dataset. Other users may have been making changes to the data included in
your dataset.

2. SET NOCOUNT ON curtails a unnecessary (in most situations) roundtrip
between the client and the server to send back the number of rows affected
by the command.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"Steven Spits" wrote in message

> Hi,
>
> When I let a TableAdapter generate stored procedure for "SELECT * FROM
> Supplier WHERE SupplierID = @SupplierID", it generates (some parts
> removed):
>
> Select:
> SET NOCOUNT ON;
> SELECT * FROM Supplier WHERE SupplierID = @SupplierID
>
> Update:
> SET NOCOUNT OFF;
> UPDATE [Supplier] SET <...> WHERE <...>;
> SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID);
>
> Insert:
> SET NOCOUNT OFF;
> INSERT INTO [Supplier] VALUES <...>;
> SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID);
>
> Delete:
> SET NOCOUNT OFF;
> DELETE FROM [Supplier] WHERE <...>;
>
> My questions:
>
> (1) Why does Visual Studio generate a SELECT after the INSERT & UPDATE
> statements?
>
> (2) Why is NOCOUNT set to ON in the select?
>
> Kind regards,
>
> Steven
>
> - - -
>
>
>
 >> Stay informed about: Questions about generated stored procedures by VS 2005 (Ta.. 

"David Browne" wrote:

>> (1) Why does Visual Studio generate a SELECT after the INSERT & UPDATE
>> statements?

> To get the results of any identity columns, computed columns, defaults or
> triggers.

And in my particular case, what happens with that data? Will the
TableAdapter update the row in the DataSet with the newly read data?

>> (2) Why is NOCOUNT set to ON in the select?

> Because setting NOCOUNT ON is a general best-practice. It's only set OFF
> for the DML to inform of the number of rows affected.

So it's only needed for statements that need to check for concurrency
violation, like DELETE, INSERT & UPDATE?

Steven

- - -
 >> Stay informed about: Questions about generated stored procedures by VS 2005 (Ta.. 

> Update:
> SET NOCOUNT OFF;
> UPDATE [Supplier] SET <...> WHERE <...>;
> SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID);
>
> Insert:
> SET NOCOUNT OFF;
> INSERT INTO [Supplier] VALUES <...>;
> SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID);
>
This behaviour of VS could be turned off. There was a checkbox somewhere in
the wizard which removes the additional SELECTs if you don't need them
 >> Stay informed about: Questions about generated stored procedures by VS 2005 (Ta..