Dynamic SQL
-
You're right. I'm so used to using sp_executeSQL (which definitely does nvarchar(max)), that I missed that exec() had been changed. I'm used to getting an error message on large data types on that one, from way back when.
But the other points, about checking the validity of the object and using QuoteName(), definitely matter.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
you all people are right at your points.
-
Whenever possible, you should use:
EXEC sp_executesql @sql [rather than EXEC(@sql)]
because it is far less susceptible to SQL injection, although not foolproof depending on what you are trying to make dynamic.
If you just need to have a stored proc run in the context of a given db, you can do that far more effectively by:
1) prefixing the proc name with sp_
2) creating the proc in the master db
3) setting the proc as a "system object"
Then you can use it from any db and it functions within the context of that db.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Sean Pearce (12/11/2012)
+1
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply