March 6, 2003 at 11:21 pm
Hello,
I have a stored procedure "spSimpleValue" which accepts 1 input parameter
when I call that stored procedure from my code(ado.net)
whats the difference between writing the stored procedure name and passing the parameters like
sampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.Int);
sampParm.Value = 10;
and just passing the stored procedure name as a simple query string like
"spSimpleValue 10"
both the above methods give the same output but whats are the advantages and disadvatages of using either of the 2 methods
Thank you.
March 7, 2003 at 4:13 am
The advantage of using a command object is being able to pass any text inc single quotes without undue processing or conversion and you can specify the CommandType (StoredProcedure or Text) to tell sql what to expect. With the execute method sql has to interpret the query to decide what it is, this can be helped by putting exec in form of procs. I read a while ago that performance could be improved by telling sql in advance what it is receiving and it seems to me that the command would be a better way.
Far away is close at hand in the images of elsewhere.
Anon.
March 7, 2003 at 8:56 am
David is roght on the mark, by executing "TEXT" queries you actually slow yourself down. SQL server goes through a series of checks to find out what it is you are executing. Is it a table? a view? a function? a stored proc? You get the idea. I may not have the order correct of the checks, but by telling sql server that you are executing a stored procedure by using the command object, sql can go straight to executing it. And since you are preparing the parameters ahead of time you are strongly typing them, so that if the types are not compatible with the data values you don't waste a trip to the server to find out. The command object tells you instantly. Last but not least, you would not be able to do output parameters calling the command as text. I may have missed some other reasons, but these are the main ones I use a strongly typed command for ALL my db calls, even when calling a view. I know with the recordset.open (VB6 here) you can tell it as well what the object is in the options param, but I still prefer the command object, as I can use it to stream as well from the view for xml querying.
Tim C.
//Will write code for food
Tim C //Will code for food
March 7, 2003 at 8:58 am
David is roght on the mark, by executing "TEXT" queries you actually slow yourself down. SQL server goes through a series of checks to find out what it is you are executing. Is it a table? a view? a function? a stored proc? You get the idea. I may not have the order correct of the checks, but by telling sql server that you are executing a stored procedure by using the command object, sql can go straight to executing it. And since you are preparing the parameters ahead of time you are strongly typing them, so that if the types are not compatible with the data values you don't waste a trip to the server to find out. The command object tells you instantly. Last but not least, you would not be able to do output parameters calling the command as text. I may have missed some other reasons, but these are the main ones I use a strongly typed command for ALL my db calls, even when calling a view. I know with the recordset.open (VB6 here) you can tell it as well what the object is in the options param, but I still prefer the command object, as I can use it to stream as well from the view for xml querying.
Tim C.
//Will write code for food
Tim C //Will code for food
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply