Dynamic Parameters in procedure
-
Hi all I have tried to do one general procedure for insert,update,delete. first i am trying insert.I have one problem:how to pass dynamic parameters and values in stored procedure? Thanks monika
-
Hi all I have tried to do one general procedure for insert,update,delete. first i am trying insert.I have one problem:how to pass dynamic parameters and values in stored procedure? Thanks monika
Could you specify a little more, what do you mean by dynamic parameters. The amount of parameters or the amount of data passed using a parameter, dynamic datatype etc. If it's possible to describe the scenario it would help. Mika
The need to optimize rises from a bad design.My articles[^]
-
Hi all I have tried to do one general procedure for insert,update,delete. first i am trying insert.I have one problem:how to pass dynamic parameters and values in stored procedure? Thanks monika
If your intention is to create dynamic SQL strings in a general procedure and execute them, I would not recommend it. First, creating dynamic strings in a .NET enviornment is a bad thing unless you use the stringbuilder class. Second, by creating strings and having the SQL engine execute them will force the SQL engine to parse and develop an execution plan for each statement, this could lead to high CPU usage on your DB server depending on how many SQL statements are being processed per second. You should try to use parameterized queries or stored procedures when dealing with a DB engine, then each time a similar query is requested the statement is already parsed and an execution plan has already been choosen, it will just substitue the parameters in and return your rows. In general, dynamic SQL bad. This is true for almost all DB engines not just MS-SQL server. Just something to think about ... :-)
-
Hi all I have tried to do one general procedure for insert,update,delete. first i am trying insert.I have one problem:how to pass dynamic parameters and values in stored procedure? Thanks monika
-
Hi all I have tried to do one general procedure for insert,update,delete. first i am trying insert.I have one problem:how to pass dynamic parameters and values in stored procedure? Thanks monika
monika_vasvani wrote:
I have tried to do one general procedure for insert,update,delete.
Bad practice. The first time the stored proc runs it gets optimised - say it happens to be an insert. The next time it runs it will use the same query plan (as it will be cached), but you may be doing an update, so the optimal query plan would be totally different. This can lead to performance issues. If you want a single proc to call for all the actions have one, but have it call a different proc for insert, another proc for update eetc.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP