December 12, 2012 at 9:02 am
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
December 12, 2012 at 11:36 pm
you all people are right at your points.
December 13, 2012 at 2:01 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 13, 2012 at 4:27 pm
Sean Pearce (12/11/2012)
+1
-- 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