Something is wrong with the code or the stored procedure

Question

To start I'll tell you this. The next question is tricky for me, but... This Stored procedures is used in two different databases. Both have the same data, let's call them older and newer.

In the older the stored procedure works fine and I even get the return. But in the Newer doesn't happen the same.

ALTER PROCEDURE dbo.actual_user
    @user char(15)
AS
    SELECT  
        US.Usercode, US.UsercodeSQL, US.LastName, US.Name, US.Nivel,
        CONVERT(int, US.Timestamp) TS,
        S.DateChanged, S.TolPSW, S.LoginsTotals, S.LoginsUsed,
        S.InitialDate, S.EndDate
    FROM    
        System_Users US
    INNER JOIN 
        Session_Users S ON US.UssercodeSQL = S.UssercodeSQL 
    WHERE   
        US.UsercodeSQL = user_id(@usuario) 
        AND S.UsercodeSQL = user_id(@usuario)

When I run an EXEC like this (by this way works in both databases):

exec actual_user 'telc\u124453'

I get this output:

Usercode                      UsercodeSQL LastName                       Name                           Nivel    TS          DateChanged           TolPSW          LoginsTotals LoginsUsed        InitialDate        EndDate
----------------------------- ----------- ------------------------------ ------------------------------ ------ ----------- ----------------------- --------------- ------------- ------------ ----------------------- -----------------------  
FESPE                         15          Brond                          Manny                          NULL   16838419    2007-04-16 16:57:00     30              10          0            NULL                    NULL

But the code I'm using doesn't work in the newer database. It's just like this.

Here is my new code. It works in the older DB but in the newer doesn't. May the version of SqlServer is not working cause of user_id from the Stored Procedure, I don't know.

Public Function UsuarioActual(ByVal datoUsuario As String) As DSUsuario.UsuarioActualRow 

    Dim ds As New DSUsuario
    Dim dt As New DataTable
    Dim sqlConn As SqlConnection = New SqlConnection()
    Dim sqlAdp As SqlDataAdapter

    Try
        sqlConn = MyBase.GetConnection
        Dim command As New SqlCommand("Usuario_Actual", sqlConn)
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.AddWithValue("@usuario", datoUsuario)
        sqlAdp = New SqlDataAdapter(command)
        sqlAdp.Fill(dt)

    Catch ex As Exception

        proc = "UsuarioActual"
        ERROR

        epn = New FactEspExceptions(ex, TipoExcepcion.ErrorBD, proc, Nothing, ex.Message)

        epn = New FactEspExceptions(ex, TipoExcepcion.ErrorBD, proc)
        epn.AuditarError()
        Throw epn

    Finally
        sqlConn.Close()
        sqlConn = Nothing
    End Try

    If ds.UsuarioActual.Rows.Count > 0 Then 
        Return ds.UsuarioActual.Rows(0)
    Else
        Return Nothing
    End If

End Function`

Edit 1: When the code runs, on the Older DB it works. But when it runs on the Newer DB, it doesn't. I don't know if it's about permissions, execSP, or something is missing. So in my point of view, i can say the problem is on the SP.

Edit 2: Running from the code I get no output. This is the error running from an EXEC on both DB.

Procedure 'actual_user' has no parameters and arguments were supplied

Edit 3: Code updated.

Answer 1

  Dim comando As String = "EXEC actual_user"


    Try
        sqlConn = MyBase.GetConnection

        Dim com As SqlCommand = New SqlCommand()

        com.CommandText = comando
        com.Connection = sqlConn
        com.CommandType = CommandType.StoredProcedure
        com.Parameters.AddWithValue("@User", usuTelecom)

The reason you're getting the error is that no parameters are being passed. This will pass the parameter in question.

The one thing I don't understand in your stored procedure is why there's a @usuario variable that doesn't seem to be declared. I'm thinking that you stripped some stuff out, though.