July 7, 2008 at 10:44 pm
Hello guys,
I m gonna create a generic stored proc. for update.
I think I should use dynamic query. Cause I don't know how many params will be passed, I think I need a string of param names, a string of param types and a string of param values. Then I build the dynamic query with these strings of parmas.
The thing is I suppose the solution above is too trivial and user need to pass many params.
Do you have example on this?
Or
any other idea on this?
Please feel free to advise me, I appreciate.
Cheers,
elton
July 8, 2008 at 12:17 am
Advice? Sure. Don't go that route.
You're adding complexity to the system with dynamic SQL and you're potentially openings a sQL injection vulnerability Dynamic SQL will require that the user has update rights on the base tables rather than just exec on the procs.You're adding overhead to the procs to generate the update statements and if (when) things go wrong they are hellishly hard to debug.
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
July 8, 2008 at 5:55 am
Well said Gail.
July 8, 2008 at 6:26 am
Gail is on the money here, totally.
Instead of trying to avoid having multiple queries, accept the fact that you will have multiple queries and then get some code to generate those queries so you don't have to write them. Most of the CUD out of the CRUD (create, read, update, delete) can be generated using any number of tools available for free or at cost (two examples, CodeSmith, Embarcadero Query Analyzer).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply