MS-SQL Stored Procedure doesn't work with empty params

Question

I have a form with inputs 'name' and 'phone'.

When they have values, everything works: a record is inserted into the database using a stored procedure, spFoo:

String sp = "spFoo 'test name','test phone'";
OdbcCommand command = new OdbcCommand(sp, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();
Response.Write(command.ExecuteNonQuery().ToString());

• Works on application
• Works on Mgmt Studio

But when they don't have values I get -1 as a response in the application:

String sp = "spFoo '',''";

• Does not work in application (-1)
• Works on Mgmt Studio

I want the user to be able to create a record without any input.

Why does this work in management studio, but not on the application?

Update: I added defaults to the params in the stored procedure, it didn't work; I gave empty strings "NULL" as values in the code, still no luck. Is this a server setting or something that won't allow empty variables?

Answer 1

You'll need to remove the SET NOCOUNT ON; from your stored procedure.

From the documentation:

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

As others have pointed out you should parameterise your query too but the -1 is caused by NOCOUNT being ON.

Edit

I realise it's not what you're asking but to use a parameterised query with ODBC you need to use ?'s as ordinal place holders as per the documentation here. For example:

using (OdbcConnection connection = new OdbcConnection(connectionString))
{
    string sp = "{call spFoo (?, ?)}";

    using (OdbcCommand command = new OdbcCommand(sp, connection))
    {
        command.CommandType = System.Data.CommandType.StoredProcedure;

        connection.Open();
        //the order here is important, the names are not!
        command.Parameters.Add("@name", OdbcType.VarChar).Value = "test name";
        command.Parameters.Add("@phone", OdbcType.VarChar).Value = "test phone";

        Console.WriteLine(command.ExecuteNonQuery().ToString());
    }
}

Answer 2

When you are calling a stored procedure from code, you should use the Parameters property on the command. Try this:

String sp = "spFoo";
command.Parameters.Add("@name", "test name");
command.Parameters.Add("@phone", "test phone");

Answer 3

As JimmyV said, you should use the command.Parameters.Add method to setup your parameters, passing in null whenever a parameter value is not specified. To address your comment about the error 'procedure or function 'spFoo' expects parameter '@name', which was not supplied', you'll also need to modify your stored procedure to use default values when a param is not supplied (e.g. null):

CREATE PROCEDURE MyStoredProcedure
    @foo int = null
AS
BEGIN
...
END

Sorry for not adding this a comment on the above post. Not enough reputation!

Answer 4

You shouldn't be calling a stored procedure the way that you currently are. You should be using parameters. Your code is susceptible to SQL injection.

Never string concat user inputted values.

What you should have, is a stored procedure setup similarly:

CREATE PROCEDURE spFoo
    @name varchar(50) = 'Jim', -- default
    @phone varchar(50) = null -- optional
AS
BEGIN
    SET NOCOUNT ON;

    -- INSERT STATEMENT

END
GO

And then supply the parameters in the code:

string name = this.nameTextBox.Text;
string phone = this.phoneTextBox.Text;

if (string.IsNullOrWhiteSpace(name))
    name = null;
if (string.IsNullOrWhiteSpace(phone))
    phone = null;

SqlConnection connection = new SqlConnection(@"<connection string>");

using (SqlCommand command = connection.CreateCommand())
{
    command.CommandType = CommandType.StoredProcedure;

    // leave this as the stored procedure name only
    command.CommandText = "spFoo";

    // if name is null, then Jim gets passed (see stored procedure definition)
    // if phone is null, then null gets passed (see stored procedure definition)
    command.Parameters.AddWithValue("@name", name);
    command.Parameters.AddWithValue("@phone", phone);

    try
    {
        connection.Open();

        int result = command.ExecuteNonQuery();

        Console.WriteLine(result);
    }
    finally
    {
        if (connection.State != ConnectionState.Closed)
            connection.Close();
    }
}

I'm not sure why you used the Odbc namespace objects since it sounds like you are using MS-SQL. You should be using objects from the System.Data.SqlClient namespace.

---

The answer to your actual question would most likely involve executing a script (not a stored procedure) similar to:

DECLARE @RC int
DECLARE @name varchar(50)
DECLARE @phone varchar(50)

-- TODO: Set parameter values here.

EXECUTE @RC = spFoo
   @name,
   @phone
GO

Which is not recommended.