SQL Server Performance

CLR Stored procedure not returning output parameter

Discussion in 'SQL Server 2005 CLR' started by EMoscosoCam, Jul 31, 2008.

  1. EMoscosoCam Member

    Hello
    Look at the following CLR Stored procedure, it just sets the value for the output parameter @Op, and this value should be returned to the client application:
    ' Server Side: CLR Stored Procedure
    <Microsoft.SqlServer.Server.SqlProcedure()> _Public Shared Sub SCN_TestXML(<Out()> ByRef Op As SqlTypes.SqlInt32, ByVal CodUser As SqlTypes.SqlInt16, ByVal Record As SqlTypes.SqlXml)
    Op = 1000 ' Value to be returned to the client application
    End Sub
    At the client side, the message box shows "3333", whereas it should show "1000":objComando.Parameters.Add(New SqlClient.SqlParameter("@Op", SqlDbType.Int, 0, ParameterDirection.InputOutput)).Value = 3333 ' Value that should be overriden
    objComando.Parameters.Add(New SqlClient.SqlParameter("@CodUser", SqlDbType.SmallInt, 0, ParameterDirection.Input)).Value = 1
    objComando.Parameters.Add(New SqlClient.SqlParameter("@Record", SqlDbType.Xml, 0, ParameterDirection.Input)).Value = "<xml/>"objComando.ExecuteNonQuery()
    MsgBox(objComando.Parameters("@Op").Value) ' this should show "1000"
    I don't understand. What could be wrong?
    Thanks a lot.
  2. moh_hassan20 New Member

    [quote user="EMoscosoCam"]objComando.Parameters.Add(New SqlClient.SqlParameter("@Op", SqlDbType.Int, 0, ParameterDirection.InputOutput)).Value = 3333 ' Value that should be overriden[/quote]

    the pararamete @op has fixed value 3333 , so it return 3333
    modified as:
    op = 1000 ' or any value that may be overwritten somwhere in the code
    ' and the next statement must be
    objComando.Parameters.Add(New SqlClient.SqlParameter("@Op", SqlDbType.Int, 0, ParameterDirection.InputOutput)).Value = op' not 1000
    .....
    ......
    objComando.ExecuteNonQuery()
    MsgBox(objComando.Parameters("@Op").Value) ' it must write "1000"

  3. EMoscosoCam Member

    Thanks, but I think you mistook the code I put in my post: The first part is the CLR Stored Procedure (therefore server-side), and the second -where I am adding parameters to a SQLCommand object) is from the client application calling that stored procedure.
    So, the sequence is as follows:
    1) In the client app I add parameters to the SQLCommand object and execute it, sending a value of 3333 to the ouput parameter.
    2) The CLR stored procedure takes that call and overrides the previous value with 1000, supposedly sending it back.
    3) After the SQL Command execution, a Message Box is displaying showing the returned value of the parameter.
    So, somehow the overriden value is not being returned to the client application.
    Any ideas?
  4. moh_hassan20 New Member

    did you test your CLR stored procedure from query analyzer.?
    If ok , review client part
    if not ok , review your CLR procedure.
  5. EMoscosoCam Member

    Thanks.
    Using the SSMS I can see that the output parameter returns succesfully. So, it turns out that somehow the SQLParameter object does not accept the value...
  6. EMoscosoCam Member

    My mistake: I was using the wrong overload of the New() method of the SQLParameter object.
  7. moh_hassan20 New Member

    hi EMoscosoCam
    I am glad that you resolved the problem

Share This Page