Procedure has no parameters and arguments were supplied 3

Question

The stored procedure returns one value:

ALTER PROCEDURE [dbo].[spCaller]
AS 
BEGIN
    DECLARE @URL nvarchar(255);  

    EXECUTE spBuscarUrl 'MIREX-2017-00001', @url = @URL OUTPUT;  

    SELECT @URL
END

When I'm trying to show the value using ASP.NET, I get an error:

Procedure spCaller has no parameters and arguments were supplied

This is my C# code:

try
{  
    string s = System.Configuration.ConfigurationManager.ConnectionStrings["dba"].ConnectionString;

    SqlConnection conexion = new SqlConnection(s);
    conexion.Open();

    using (SqlCommand cmd = new SqlCommand("spCaller",conexion))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@URL", SqlDbType.NVarChar).Value = Label1.Text.Trim();

        object o = cmd.ExecuteScalar();

        if(o != null)
        {
            string id = o.ToString();
            lblTitulo.Text = "Completed";
        }
    }
}
catch (Exception ex)
{
    throw new Exception(ex.Message);
}

Answer 1

The error message is pretty clear that your stored procedure does not expects any parameter while from c# code you are passing parameter.

Actually you have specified a variable in your stored procedure not a parameter, you will need to specify it just after the SP name parameter with it's datatype and in your case OUTPUT keyword as it output parameter.

It should be :

ALTER procedure [dbo].[spCaller] @URL NVarChar(255) OUTPUT
.......
.......

Your final stored procedure would be like:

ALTER procedure [dbo].[spCaller] @URL NVarChar(255) OUTPUT
AS BEGIN
EXECUTE spBuscarUrl  
    'MIREX-2017-00001', @url = @URL OUTPUT;  
select @URL
END

You can have a look at this post explaining how to pass different kind of parameters to a Stored Procedure.

Hope it helps!

Answer 2

This line is the error:

cmd.Parameters.Add("@URL", SqlDbType.NVarChar).Value = Label1.Text.Trim();

@URL is not a param in your stored procedure.

Check the correct syntax here: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql

CREATE PROCEDURE What_DB_is_that 
     @ID int   
AS    
    SELECT DB_NAME(@ID) AS ThatDB;