One parameter too many

  • Thread starter Thread starter Michael C
  • Start date Start date
M

Michael C

I was looking over some code that calls a stored procedure from C#. I have
specified one more parameter than necessary but the code still works. Does
anyone know why it works? This is on a PDA connecting to SQLServer.

Thanks.

eg

sqlCommand command = new SqlCommand(connection, "INSERT INTO SomeTable
(A,B,C) VALUES (@A, @B, @C)");
create parameter called @A (couldn't be bothered typing code)
create parameter called @B
create parameter called @C
create parameter called @D
command.Execute.
 
Michael,

Just because a parameter isn't used doesn't mean that the SQLCommand becomes
invalid. If you've generated the SQL statement somehow, then it might be that
you use @D sometimes.

But typically this is bad practice, of course. Better to call a stored
procedure, so that SQL can prepare it better.

Rob
 
Mike
Your INSERT statement does not violate syntax error. You have specified
three columns and you have provided three values
If you try INSERT INTO (A,B,C) VALUES (@A, @B, @C,@D) you get an error.
 
Michael said:
I was looking over some code that calls a stored procedure from C#. I have
specified one more parameter than necessary but the code still works. Does
anyone know why it works? This is on a PDA connecting to SQLServer.

Thanks.

eg

sqlCommand command = new SqlCommand(connection, "INSERT INTO SomeTable
(A,B,C) VALUES (@A, @B, @C)");
create parameter called @A (couldn't be bothered typing code)
create parameter called @B
create parameter called @C
create parameter called @D
command.Execute.
Click to expand...
When using parameters, wont it just subst the relevant params and ignore
others?
Lets test....
Nope, when using query analyzer it doesnt work, too many params.
However as someone so nicely said earlier, ado.net uses sp_executesql
behind the scenes (dont know if its true) so if thats the case, then any
redundant params would be ignored, because it would only match
parameters by name, not just try and pass in added order etc...

ie (butchered from BOL)
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint, @L2 int',
@level = 35,
@L2 = 10

@L2 is redundant but is passed anyway and ignored

JB
:)
 
Back
Top