February 28, 2009 at 9:56 pm
Hi All,
Is there a way to pass sql statment(i.e. insert, update, delete) to a stored procedure, and how the stored procedure is gonna execute it?
On the other hand, which one would affect the performance, to pass a list of variables with their corresponding values, or passing a statment block to a stored procedure.
Iwould be so glad to get a soon answer.
Thx.
March 1, 2009 at 5:37 am
Why do you want to pass an entire statement to a stored procedure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 1, 2009 at 7:06 am
For two reasons.
1. To reduce the total number of stored procedures in my DB.
2. And i want to compare the performance whether it 'll be increaed or decreased.
March 1, 2009 at 10:26 am
Performance-wise it'll be about the same. However, as soon as you start using dynamic SQL you're creating a whole bunch of security issues and concerns, from greater permissions required (permissions needed on the base tables rather than just on the procedures) up to the possibility of SQL injection.
Unless you're talking a couple thousand stored procedures, having fewer procedures is not worth the trouble.
Have a good read through this - http://www.sommarskog.se/dynamic_sql.html
Oh, one other thing. SQL code embedded in a front-end app is a nightmare to tune, because it's hard to see all the possible calls and even harder to change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2009 at 5:11 am
March 2, 2009 at 6:26 am
A lot of them!!!
March 2, 2009 at 6:47 am
ammarghanem921 (3/2/2009)
A lot of them!!!
How many?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2009 at 6:49 am
More than 300!!
March 2, 2009 at 6:57 am
ammarghanem921 (3/2/2009)
More than 300!!
That's nothing. Last system I worked on (which did use dynamic SQL to reduce procedure count) had over 3000.
Rather write the procedures. The downsides of dynamic SQL aren't worth saving a couple hundred procs. Did you read the link I gave? Do you understand the risks of going with dynamic SQL?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2009 at 6:58 am
Regardless the advantage or disadvantage of doing that, i want to know, is it possible or not, and how to do it!
March 3, 2009 at 7:11 am
ammarghanem921 (3/3/2009)
is it possible or not, and how to do it!
It is possible and if you read the article I linked a couple days back you'd see how to do it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply