Difference between adding parameters to stored procedure in SQL Server?

Question

I would like to know the difference between these 2 notations.

First of all I have a stored procedure

CREATE PROCEDURE AddSomething( @zonename varchar(50), @desc varchar(255), @TheNewId int OUTPUT ) AS 
BEGIN 
   INSERT INTO a_zone(zonename, descr) VALUES(@zonename, @desc) 
   SELECT @TheNewId = SCOPE_IDENTITY()         
END

What is the difference if I add parameters in this manner

SqlCommand Cmd = new SqlCommand("AddSomething", oConn); 
Cmd.CommandType = CommandType.StoredProcedure; 
SqlParameter oParam1 = Cmd.Parameters.AddWithValue("@zonename", sName);
SqlParameter oParam2 = Cmd.Parameters.AddWithValue("@desc", description);

and

SqlCommand Cmd2 = new SqlCommand("AddSomething", oConn); 
Cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.Add("@zonename", SqlDbType.VarChar).Value = zonename.Text.Trim();
cmd2.Parameters.Add("@desc", SqlDbType.VarChar).Value = desc.Text.Trim();

Answer 1

Here are some explanations:

difference between command Add and AddWithValue

Dim cmd as new SqlCommand("SELECT * FROM MyTable WHERE MyDate>@TheDate",conn)
cmd.Parameters.Add("@TheDate",SqlDbType.DateTime).Value="2/1/2007"

vs

cmd.Parameters.AddWithValue("@TheDate","2/1/2007")

"Add forces the conversion from string to date as it goes into the parameter. AddWithValue would have simply passed the string on to the SQL Server.

When using Parameters.Add - the SqlDbType is known at compile time

When using Parameters.AddWithValue the method has to box and unbox the value to find out its type.

Additional benefits of the former is that Add is a bit more code safe and will assist against SQL injection attacks , code safe in terms that if you try to pass a value that doesn't match the SqlDb type defined - the error will be caught in .Net code and you will not have to wait for the round trip back.

• http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx
• http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

Edit:

example to get an Output-Parameter:

C#

cmd.Parameters.Add(new SqlParameter("@TheNewId", SqlDbType.Int, int.MaxValue));
cmd.Parameters("@TheNewId").Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int theNewID = (int)cmd.Parameters("@TheNewId").Value;

VB.Net

cmd.Parameters.Add(New SqlParameter("@TheNewId", SqlDbType.Int, Int32.MaxValue))
cmd.Parameters("@TheNewId").Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Dim theNewID As Int32 = DirectCast(cmd.Parameters("@TheNewId").Value, Int32)

Answer 2

When you use AddWithValue, the datatype will be worked out (as best possible) based on the types of the variables passed to the method - assuming sName and description are string variables, the params will be passed in as NVARCHAR.

I personally prefer the 2nd approach, being explicit with the data types (plus I actually specify the sizes too) so that they are guaranteed to match the sproc definition and avoid any unexpected behaviour.