CODEWITHSUNDEEP
c#sqlstored-procedures
nandu
nandu

Reputation: 5

Error - stored procedure has no parameters and arguments were supplied in c#

When I'm trying to call store procedure and return the value from the procedure, I'm getting the error message - procedure has no parameters and arguments were supplied

Below is the c# code:

using (SqlCommand command2 = new SqlCommand("getservername8", conn1))
{
    command2.CommandType = CommandType.StoredProcedure;
    command2.Parameters.Add("@s", SqlDbType.NVarChar, 500);
    command2.Parameters["@s"].Direction = ParameterDirection.Output;
    command2.ExecuteNonQuery();
    string server = (string)command2.Parameters["@s"].Value;
}

Below is the stored procedure:

GO
ALTER procedure [dbo].[getservername9]
@s varchar(50)
as begin
declare @server_name varchar(500)
select  @server_name = short_description from [Event_alerts].[dbo].[event_alerts]
select @s= SUBSTRING(@server_name, CHARINDEX('-', @server_name) + 15, 50)
return @s
end

Stored procedure gets executed with no error.Any help will be much appreciated

Upvotes: 0

Views: 1688

Answers (3)

MBB
MBB

Reputation: 1685

Please make below changes to your code -

  1. Mark your variable in the stored procedure as output like below -

     @s varchar(50) output

  2. You cannot return varchar output values like you added in stored procedure. Stored procedures always return integer values when you use return statement here. In your case you will get below error when executed - (the same can be observed in .NET end as well).

    declare @s varchar(50)         
exec [dbo].[getservername9] @s

Conversion failed when converting the varchar value '' to data type int.

  1. Remove the return statement from stored procedure which will automatically return the value back to .NET code.

  2. Finally make the stored procedure names consistent in both .NET and SQL procedure.

Upvotes: 1

sayah imad
sayah imad

Reputation: 1553

First is to change your store procedure name, in the code you are using getservername8 while your stored procedure name is getservername9, the second point you need to mark your parameter as output as show in the code below

Code : 

using (SqlCommand command2 = new SqlCommand("getservername", conn1))
{
   command2.CommandType = CommandType.StoredProcedure;
   command2.Parameters.Add("@s", SqlDbType.NVarChar, 500);
   command2.Parameters["@s"].Direction = ParameterDirection.Output;
   command2.ExecuteNonQuery();
   string server = (string)command2.Parameters["@s"].Value;
}

Stored Procedure :

GO
ALTER procedure [dbo].[getservername]
@s varchar(50) output
as begin
declare @server_name varchar(500)
select  @server_name = short_description from [Event_alerts].[dbo].[event_alerts]
select @s= SUBSTRING(@server_name, CHARINDEX('-', @server_name) + 15, 50)
return @s
end

Upvotes: 0

JyothiJ
JyothiJ

Reputation: 315

Use ExecuteScalar instead of Executenonquery. Please refer..

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?view=dotnet-plat-ext-3.1

Upvotes: 1

Related Questions