SQL Server procedure has too many arguments specified

Question

I get the error

Procedure or function has to many arguments specified

but only sometimes. It's a REST API and cellphones call this function to validate the user. Today it's onli 1-2 cellphones and they make the call every second minute.

It works well most times but sometimes I get this error code.

To see what's wrong, I've made a small loop that gathers the parameters into a string. And sometimes they are two @GUID and @Datum and sometimes repeat themselves.

Here is the VB code:

Public Shared Sub validatePhone(secretKey As String, ByRef _RS As phoneData, Fnr As Integer)
    Dim connStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStringSQL")
    Dim Conn As SqlConnection = New System.Data.SqlClient.SqlConnection(connStr)
    Dim _theString As New System.Text.StringBuilder

    Try
        _RS.message = ""
        _RS.status = True
        Dim Status As Integer = 0
        Dim _GUID As Guid = New Guid(secretKey)

        Try
            Conn.Open()
        Catch ex As Exception
        End Try

        cmd.Connection = Conn
        Apt.SelectCommand = cmd
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "API2017_checkPhone"

        cmd.Parameters.Clear()
        cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier).Value = _GUID
        cmd.Parameters.Add("@Datum", SqlDbType.SmallDateTime).Value = Now

        For Each item As SqlParameter In cmd.Parameters
            _theString.Append(item.ParameterName & ";")
        Next

        Apt.Fill(ds, "DataCheckPhone")

        If ds.Tables("DataCheckPhone").Rows.Count > 0 Then
            With ds.Tables("DataCheckPhone").Rows(0)
                Status = .Item("spStatus")

                If Status = 0 Then
                    _RS.Namn = .Item("Namn")
                    _RS.SalesID = .Item("SalesID")
                    _RS.Anlaggning = .Item("Anlaggning")
                    _RS.Anlnr = .Item("Anlnr")
                    Funktions.URLEncodeStr(_RS.Namn)
                    Funktions.URLEncodeStr(_RS.Anlaggning)
                End If
            End With
        Else
            Dim _Lnum As Integer
            Funktions.Logg(Fnr & ": " & "Fatal error", 999, _Lnum, 0)
            _RS.message = "Error 999:" & _Lnum.ToString
            Return
        End If

    Catch ex As Exception
        _RS.status = False
        _RS.Anlaggning = Nothing
        _RS.Anlnr = 0
        _RS.Namn = Nothing
        _RS.SalesID = Nothing
        Dim _Lnum As Integer
        Funktions.Logg(Fnr & ": " & ex.Message & "{" & _theString.ToString & "}", 999, _Lnum, 0)
        _RS.message = "Error 999:" & _Lnum.ToString

    Finally
        ds.Tables.Clear()

        Try
            ds.Tables("DataCheckPhone").Dispose()
        Catch ex As Exception
        End Try
    End Try

    Try
        Conn.Close()
        Conn.Dispose()
    Catch ex As Exception
    End Try
End Sub

And here is the stored procedure:

ALTER PROCEDURE [dbo].[API2017_checkPhone]
    @GUID as uniqueidentifier,
    @Datum as smalldatetime
AS
    DECLARE @Status AS INT
    DECLARE @Anlaggning AS NVARCHAR(50)
    DECLARE @Namn AS NVARCHAR(50)
    DECLARE @Anlnr AS INT
    DECLARE @SalesID AS uniqueidentifier
    DECLARE @LockedSalesman AS BIT
    DECLARE @LockedAnlaggning AS BIT

    SET @Status = 0

    IF EXISTS (SELECT * FROM KK2017_connectedPhones WHERE [guid] = @guid)
    BEGIN
        SET @status = 0
        SET @salesID = (SELECT salesID FROM KK2017_connectedPhones 
                        WHERE [guid] = @guid)
        SET @Anlnr = (SELECT anlnr FROM KK2017_Säljare WHERE salesID = @salesID)
        SET @Namn = (SELECT Namn FROM KK2017_Säljare WHERE salesID = @salesID)
        SET @Anlaggning = (SELECT namn FROM KK2017_Anlaggning WHERE anlnr = @Anlnr)
        SET @LockedSalesman = (SELECT locked FROM KK2017_Säljare WHERE salesID = @salesID)

        UPDATE KK2017_Säljare 
        SET inloggad = @Datum 
        WHERE salesID = @SalesID

        IF @LockedSalesman = 1 
        BEGIN
            SET @Status = 2
        END

        SET @LockedAnlaggning = (SELECT locked FROM KK2017_Anlaggning 
                                 WHERE AnlNr = @Anlnr)

        IF @LockedAnlaggning = 1
        BEGIN
            SET @status = 3
        END
    END
    ELSE
       SET @status = 1

    SELECT 
        @Status AS spStatus, 
        @Anlaggning AS Anlaggning, 
        @anlnr AS anlnr, 
        @Namn AS namn, 
        @SalesID AS salesID

I must have done something wrong but can not see it.

If anyone has a proposal, I would be grateful.

/Claes

Answer 1

You have a threading issue because you have defined cmd as shared, which means there is only one instance servicing all of the incoming requests. Sometimes two threads are in the process of adding parameters at the same time, and both threads end up with four, hence the error.

The standard pattern here would be declare cmd as a local variable, and instantiate a new instance each time the function is called. The overhead to this is negligible.

Also, avoid shared variables in multithreaded applications (such as web services). There is only one copy of the variable for all users and all threads, and 99% of the time that is not what you actually want. For the other 1% usually you'd use application variables or HttpCache. In this case you should use a local variable.