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();

TylerH · Accepted Answer · 2020-06-18 19:19:46Z

10

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.

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)

edited Jun 18, 2020 at 19:19

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Mar 26, 2011 at 22:22

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sreedhar Danturthi Over a year ago

@Tim so u mean both methods are used for passing parameters into stored procedure.

Tim Schmelter Over a year ago

@user653622: Yes, but not only for stored-procedures but every kind of sql-command. They both have advantages and disadvantages(see links).

Sreedhar Danturthi Over a year ago

@Tim How can I use the second method for passing output type parameters especially when I won't have any value to pass but instead I would be expecting a value from stored procedure. Specifically in this case cmd2.Parameters.Add("@TheNewId", SqlDbType.Int).Value = ???; If i use the second method it would be expecting a value but i have to tell it explicitly don't expect a value u r a output parameter, how should i tell this ? Please help me

Sreedhar Danturthi Over a year ago

@Tim ur example won't work in asp.net/c# as SqlParameter doesn't have that overload method. Instead this procedure works SqlParameter outputParameter1 = new SqlParameter("@NewIntId", SqlDbType.Int); outputParameter1.Direction = ParameterDirection.Output; cmd2.Parameters.Add(outputParameter1); cmd2.ExecuteNonQuery(); int valueFromOutputParameter = (int)cmd2.Parameter["@NewIntId"].Value

AdaTheDev · Accepted Answer · 2011-03-26 22:24:41Z

7

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.

answered Mar 26, 2011 at 22:24

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

1 Comment

marc_s Over a year ago

+1 exactly - rather be explicit than let too much "magic" determine your types. Also: if you need to pass in e.g. DBNull.Value - how would the ADO.NET runtime be able to figure out what type your "NULL" is going to be!?!?! Be explicit - a tiny bit more typing, but a lot more safety and clarity (think: maintenance!)

Collectives™ on Stack Overflow

Difference between adding parameters to stored procedure in SQL Server?

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related