Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Stored procedure or function expects parameter which is not supplied

I am trying to insert data into a SQL Server database by calling a stored procedure, but I am getting the error

Procedure or function 'SHOWuser' expects parameter '@userID', which was not supplied.

My stored procedure is called SHOWuser. I have checked it thoroughly and no parameters is missing.

My code is:

public void SHOWuser(string userName, string password, string emailAddress, List<int> preferences) {         SqlConnection dbcon = new SqlConnection(conn);          try         {              SqlCommand cmd = new SqlCommand();              cmd.Connection = dbcon;             cmd.CommandType = System.Data.CommandType.StoredProcedure;             cmd.CommandText = "SHOWuser";              cmd.Parameters.AddWithValue("@userName", userName);             cmd.Parameters.AddWithValue("@password", password);             cmd.Parameters.AddWithValue("@emailAddress", emailAddress);              dbcon.Open();              int i = Convert.ToInt32(cmd.ExecuteScalar());              cmd.Parameters.Clear();             cmd.CommandText = "tbl_pref";              foreach (int preference in preferences)             {                 cmd.Parameters.Clear();                 cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(i));                 cmd.Parameters.AddWithValue("@preferenceID", Convert.ToInt32(preference));                  cmd.ExecuteNonQuery();             }         }         catch (Exception)         {             throw;         }         finally         {             dbcon.Close();         } 

and the stored procedure is:

ALTER PROCEDURE [dbo].[SHOWuser] (     @userName varchar(50),     @password nvarchar(50),     @emailAddress nvarchar(50) ) AS BEGIN     INSERT INTO tbl_user(userName, password, emailAddress)      VALUES (@userName, @password, @emailAddress)      SELECT         tbl_user.userID, tbl_user.userName,         tbl_user.password, tbl_user.emailAddress,          STUFF((SELECT ',' + preferenceName                 FROM tbl_pref_master                INNER JOIN tbl_preferences ON tbl_pref_master.preferenceID = tbl_preferences.preferenceID                WHERE tbl_preferences.userID = tbl_user.userID                FOR XML PATH ('')), 1, 1, ' ' ) AS Preferences     FROM         tbl_user      SELECT SCOPE_IDENTITY(); END 

This is the second stored procedure tbl_pref which is used in the same function:

ALTER PROCEDURE [dbo].[tbl_pref]     @userID int,     @preferenceID int AS BEGIN     INSERT INTO tbl_preferences(userID, preferenceID)      VALUES (@userID, @preferenceID) END 
like image 370
user2920046 Avatar asked Oct 31 '13 10:10

user2920046


People also ask

Can a stored procedure have no parameters?

The simplest kind of SQL Server stored procedure that you can call is one that contains no parameters and returns a single result set. The Microsoft JDBC Driver for SQL Server provides the SQLServerStatement class, which you can use to call this kind of stored procedure and process the data that it returns.

What is the function of stored procedure and what a stored procedure is not supposed to do?

Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters for it whereas Procedures can have input/output parameters . Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..

Does a stored procedure have to have input parameters?

The main difference between these objects is that function has a return value, and procedure has not. A stored procedures and functions may have input, output, and input/output parameters.

Can stored procedure take parameter?

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.


2 Answers

In my case I received this exception even when all parameter values were correctly supplied but the type of command was not specified :

cmd.CommandType = System.Data.CommandType.StoredProcedure; 

This is obviously not the case in the question above, but exception description is not very clear in this case, so I decided to specify that.

like image 191
Alex Krotnyi Avatar answered Sep 21 '22 16:09

Alex Krotnyi


I came across this issue yesterday, but none of the solutions here worked exactly, however they did point me in the right direction.

Our application is a workflow tool written in C# and, overly simplified, has several stored procedures on the database, as well as a table of metadata about each parameter used by each stored procedure (name, order, data type, size, etc), allowing us to create as many new stored procedures as we need without having to change the C#.

Analysis of the problem showed that our code was setting all the correct parameters on the SqlCommand object, however once it was executed, it threw the same error as the OP got.

Further analysis revealed that some parameters had a value of null. I therefore must draw the conclusion that SqlCommand objects ignore any SqlParameter object in their .Parameters collection with a value of null.

There are two solutions to this problem that I found.

  1. In our stored procedures, give a default value to each parameter, so from @Parameter int to @Parameter int = NULL (or some other default value as required).

  2. In our code that generates the individual SqlParameter objects, assigning DBNull.Value instead of null where the intended value is a SQL NULL does the trick.

The original coder has moved on and the code was originally written with Solution 1 in mind, and having weighed up the benefits of both, I think I'll stick with Solution 1. It's much easier to specify a default value for a specific stored procedure when writing it, rather than it always being NULL as defined in the code.

Hope that helps someone.

like image 40
ubercam Avatar answered Sep 23 '22 16:09

ubercam