Databases
StackOverflow2013
Postsdbo
POReturning rowcount from CLR stored procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReturning rowcount from CLR stored procedure
    primarykey
    data
    text
    <p>If I have a stored procedure like this:</p> <pre><code>CREATE PROCEDURE [dbo].[sp_update_dummy] AS BEGIN update update_dummy set value = value + 1 where id = 1 END </code></pre> <p>and call this using <code>executeUpdate</code> (from standard java.sql library) then the updated row count is returned to the Java program (assuming, of course, that the update statement updates a row in the table).</p> <p>However if I execute a CLR stored procedure coded like this:</p> <pre><code>[Microsoft.SqlServer.Server.SqlProcedure] public static void clr_update_dummy() { using (SqlConnection conn = new SqlConnection("context connection=true")) { SqlCommand command = new SqlCommand("update update_dummy set value = value + 1 where id = 1", conn); conn.Open(); command.ExecuteNonQuery(); conn.Close(); } } </code></pre> <p>Then the Java program does not get the updated row count (it seems to get a value of -1 returned). This is also what happens if I put <code>SET NOCOUNT ON</code> into the SQL stored procedure.</p> <p>So it looks to me that a CLR stored procedure acts as if <code>SET NOCOUNT ON</code> is used.</p> <p>Is there any way to code a CLR stored procedure so that row count can be picked up in the same way it is for a SQL stored procedure? Unfortunately it isn't possible to change the Java program (it is a 3rd party component) to, for example, pick up an OUTPUT parameter. I've looked at SqlContext.Pipe but there is nothing obvious there. Also I'm not sure of the mechanism by which the row count is returned to the executeUpdate procedure.</p> <p>I can probably create a hack to get around the problem (Java executes a SQL stored procedure which in turn executes a CLR stored procedure for instance) but if possible I'd like to not introduce another layer into the call stack.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USKeith Miller
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COSide note: You shouldn't use the `sp_` prefix for stored procedure names - it's reserved for Microsoft's system procedures.
      singulars
      1. POReturning rowcount from CLR stored procedure
      1. USDamien_The_Unbeliever
    2. CO@Damien_The_Unbeliever - I'm aware of that, I chose the name unwisely.
      singulars
      1. POReturning rowcount from CLR stored procedure
      1. USKeith Miller
    3. CODo you need to use CLR? Is this simply to avoid using SQL?
      singulars
      1. POReturning rowcount from CLR stored procedure
      1. USgbn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload