Anything wrong with dynamic SQL in stored proc?

I have a co-worker who says dynamic sql shouldn't be used in stored procs because it defeats the purpose of using SQL. He says it doesnt make sense. He says the dynamic sql should be build in code (C#/ASP.Net in this case) and just run it in the code...


I dont see an issue with using dynamic sqls in stored procs. Any pros/cons?
LVL 8
CamilliaAsked:
Who is Participating?
 
RainverseCommented:
Have a look at this page. It pretty much covers it all: http://msdn.microsoft.com/en-us/library/ff648339.aspx
0
 
RainverseCommented:
I don't think it really matters from a performance point of view. Neither is compiled or checked for errors until execution. From a security point of view neither should be used without validating the input against SQL injection.

-MJC
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
It really depends on the situation. I have dynamic SQL in astored proc to basically denormalize a table just so I can look at it. But we don't ever use it from a real application because it's easy to do in c#.

The main reason for dynamic SQL is because you make the column names on the fly. Which means you have to select * which is all generally considered a nono. One reason is because if the table gets a column added it can break you whole app.

Additionally I don't know when I'd use dynamic SQL OUT of a stored proc. so really we are talking about dynamic SQL in general.
0
 
CamilliaAuthor Commented:
>>From a security point of view neither should be used without validating the input against SQL injection.

do you have an example?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.