Is it necessary to add a @ in front of an SqlParameter name?

Question

In one of our application the parameters passed to a stored procedure in this way

Dim parm As New SqlParameter("searchText", SqlDbType.VarChar)
parm.Direction = ParameterDirection.Input
parm.Size = 50
parm.Value="test"

cmd.Parameters.Add(parm)

and the procedure contains a parameter as @searchText

i.e. the parameter name passed from the code is searchText and that in the stored procedure is @searchText .

But it is working properly, I am always getting the required results.

So my question is like so there is no need to specify @ before the parameter? Whether it will append @, can anyone please give an answer for this.

score 35 · Accepted Answer · edited Dec 03 '19 at 10:27

35

According to the documentation, the name must start with an @:

The ParameterName is specified in the form @paramname.

According to the source code (have a look at SqlCommand and SqlParameter.ParameterNameFixed in the reference source), an @ is added automatically, if needed.

So yes, it works, but it's an undocumented feature. Best practice recommends that you do not rely on this and manually prefix your parameter name with an @.

edited Dec 03 '19 at 10:27

GSerg

76,472
17
159
346

answered Apr 20 '12 at 11:43

Heinzi

167,459
57
363
519

So you are telling that it will automatically add @ before the parameter name. right? – Mahesh KP Apr 20 '12 at 11:50
4

@mahesh: In its current implementation, as far as I can see, yes. Since it's an undocumented feature, it might change at any time (for example, in a new framework version) without notice. – Heinzi Apr 20 '12 at 11:51
@mahesh: How it happens? When SqlCommand creates its internal data structures, it accesses `parameter.ParameterNameFixed` instead of `parameter.ParameterName`. ParameterNameFixed is an internal property that basically returns `If(ParameterName.StartsWith("@"), ParameterName, "@" & ParameterName)`. – Heinzi Apr 20 '12 at 11:55
ok, but i cant find any other documentation regarding this other than this link http://typedescriptor.net/browse/members/402789-System.Data.SqlClient.SqlParameter.ParameterNameFixed#. Can you please give any other links – Mahesh KP Apr 20 '12 at 12:02
4

@mahesh: It's an **undocumented** feature. That means it's not officially documented. ;-) – Heinzi Apr 20 '12 at 12:10
7

@Heinzi: Got a little chuckle for ya... using JustDecompile to look at the source for `System.Data.SqlClient.SqlParameter`... there's an internal property named `ParamaterIsSqlType`... looks like somebody at Microsoft needs a spelling lesson :-) – InteXX Feb 22 '15 at 03:06
Quoting @Mahesh: "Since it's an undocumented feature, it might change at any time (for example, in a new framework version) without notice." You're not been realistic. Microsoft will _never_ change this feature. Otherwise it would break a lot of existent code. – drowa Jun 09 '15 at 19:59
Unfortunately we can't see comments like "TABS are ***ing up the ***ing compiler!!!!" in the MS code anymore. :-) – tmighty Sep 22 '17 at 22:01
Maybe this changed at some point since this answer, maybe in .NET Core, but prepending the `@` isn't passing the param correctly in my project. It's also possible that we've got a specific usage difference that isn't compatible. – brianary Mar 30 '21 at 18:15
See my answer. It's optional in one case and not optional in another. – ATL_DEV Jan 06 '22 at 05:54

Niranjan Singh · Answer 2 · 2012-04-20T13:08:25.200

Ref: SqlParameter.ParameterName Property and IDataParameter.ParameterName Property

The ParameterName is specified in the form @paramname. You must set ParameterName before executing a SqlCommand that relies on parameters. If you are using Sql Server as Database then you must specify @ before the parameter name.

your parameter name must be same as at backend eg. you have @searchText then in your parameter specification it must be SqlParameter("@searchText" ..

your code should be like this

Dim parm As New SqlParameter("@searchText", SqlDbType.VarChar)
parm.Direction = ParameterDirection.Input
parm.Size = 50
parm.Value="test"

cmd.Parameters.Add(parm)

Note: Oracle and SqLite use different use different character to specify parameter and there may be @ symbol is not used specified by the specification of ado.net.

Edit: By comments

As you specified the link, it is also some sort of fix, but as per the msdn documentation, you must specify the positional parameter with '@' whether you are using any data provider oledb, sql, odbc. Ref

if (0 < parameterName.get_Length() && '@' != parameterName.get_Chars(0))
        {
            parameterName = "@" + parameterName;
        }

yes , i know it should be @ searchText, but my question is if i am using parameter name searchtext, then also i am getting the correct results.. — Mahesh KP, Apr 20 '12 at 11:49
Your answer says that case matters in the parameter's name. Is that actually the case? It seems to my recollection that it doesn't matter if case matches. — Andrew Barber, Apr 20 '12 at 12:22
@Andrew Barber: Not case but but passing parameters without @ in the beginning — Mahesh KP, Apr 23 '12 at 03:00

score 0 · Answer 3 · answered Jun 04 '14 at 20:22

Its not compulsory to specify the @. However, its a best practice.

Its similar in analogy to strings. There certainly is no harm in defining strings as such in .NET:

string s;
//Rest of the code follows;

But again, its a best practice to define them as :

string s = string.Empty;

You see, its a question of conventions and best practices!!!

score 0 · Answer 4 · answered Apr 30 '15 at 19:28

0

I recommended you to use add "@" marker with your parameter name. SqlParameter helps to add automatically, but others' parameter might not to.

answered Apr 30 '15 at 19:28

tomexou

343
2
5

score 0 · Answer 5 · answered Jan 06 '22 at 05:51

Is the "@" symbol required? Yes and No. When you add a parameter using DbCommand, it's optional regardless of whether you're using SQL Server or not:

// Look Ma no @ required!    
DbCommand command = database.GetStoredProcCommand("StoredProctologistAndGambler");
database.AddInParameter(command, "Bet", DbType.Int32, fromLineNumber);
database.AddOutParameter(command, "Diagnosis", DbType.String, -1);

If you're going to reference the command later, however, the "@" prefix is required. Microsoft figured it was to hard to carry it over to the rest of the API.

var examResult =  command.Parameters["@Diagnosis"];   // Ma! Microsoft lied! It requires the "@" prefix.

Is it necessary to add a @ in front of an SqlParameter name?

5 Answers5

Linked

Related