August 21, 2003 at 1:37 am
In SQL client MS has not built in support for un-named parameters. Named parameters is default and the only way to go.
I have tried mimicking un-named parameters by doing:
dim cmd as new SQLCommand("exec someProc @p1, @p2, @p3",con)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add(new SqlParameter("@p1",value1))
cmd.Parameters.Add(new SqlParameter("@p2",value2))
cmd.Parameters.Add(new SqlParameter("@p3",value3))
However what gets sent to SQL server is this:
exec sp_executesql N'EXEC dbo.RetrieveImagesSP @p1', N'@p1 varchar(4)', @p1 = '9952'
What are the drawbacks of this usage of sp_executesql?
Should I instead use stringconcatenating in the host language and try to send only:
EXEC dbo.RetrieveImagesSP '9952' ?
Any other suggestions?
/m
August 21, 2003 at 5:32 pm
SP_executesql isnt that bad, it does try to re-use the query plan, but not as good as a stored proc - both because the proc is already compiled and because it represents much less security risk than sending code over. If you take a look at their data access block on MSDN it wraps a lot of that code into a simple call, plus it gives you a great abstraction layer for data access. Look for an article on it soon here as well.
Andy
August 22, 2003 at 12:24 am
Before .NET we had a DAL with support both for named and unnamed parameters. That became possible with ADO 2.6.
With ADO.NET I can have named parameters only with SQL client and unnamed parameters only with the OLEDB provider.
I have looked at MS data access block. It gives no support for returnvalues in disconnected mode. It does not give support for unnamed parameters. So I will write my own.
Since about half of our procs do not have optional parameters I would like to have support for unnamed parameters. (I hope that improves performance) And I do not want to use the OLEDB provider in those cases.
So what is the best way to mimick ADO.2.6 way of sending positional parameters?
Like this: EXEC someProc '123'
August 25, 2003 at 4:03 am
SP_executesql probably the easiest way. I almost always typed my params before, only time I didnt was the occasional "cn.execute procname" with no params, other than that it was always a command object. Whats your reason for wanting the unnamed option?
Andy
August 25, 2003 at 6:13 am
My reasons for sending un-named parameters are 2:
1. I suspect it is better to send as little data as possible over the network. Why send the parameter names if you dont have to?
2. I do not like the fact that the names of the inparameters are hardcoded in the VB-code. Bad encapsulation.
(I aggree that the order of the parameters is hardcoded but there is no way out of that)
/m
August 26, 2003 at 5:23 am
I agree less is better, but I'd bet that since you still have to send it a packet at a time, you can probably get the param name in the same packet.
Why do you consider that bad encapsulation? Once you're forced to rely on ordinal positioning, can anything be worse? You could always do parameters.refresh instead of hard coding the params - which is the approach the DAB takes (except it caches them), but even if you refresh, if the params change, how is your app going to know what to change? I'll grant that you could have yet another lookup to map something into the new/changed param and there are times when that approach makes sense, but the largest part of my procs arent that likely to change, I'd rather have nice clear stable code.
My two cents worth.
Andy
August 26, 2003 at 6:27 am
Agree with Andy. It is better to rely on a hardcoded parameterNAME than on an ordinal.
After all, you would not write an order by clause using ordinals, would you?
After all, the names of your tables and columns are 'hardcoded' there too, just as they are in your VB Code...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply