Timeline for Is there a way to force Deferred Name Resolution even if the table exists when creating a stored procedure?
Current License: CC BY-SA 3.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 2, 2017 at 19:35 | history | edited | marc_s | CC BY-SA 3.0 |
added 47 characters in body; edited title
|
| Feb 11, 2016 at 17:42 | answer | added | Geoff Patterson | timeline score: 7 | |
| Feb 28, 2012 at 13:45 | vote | accept | Daniel James Bryars | ||
| Feb 23, 2012 at 15:17 | answer | added | Brent Ozar | timeline score: 9 | |
| Feb 23, 2012 at 14:09 | comment | added | Aaron Bertrand | Well you can have a transaction around DDL as well as DML, but the changes still need to take place in the right order. (This is how tools like Red-Gate's SQL Compare do it - they can roll back all their schema mods easily when they come across an error.) There was a more recent question about this, you may want to watch it in case someone comes up with a better answer than mine: dba.stackexchange.com/questions/13648/… | |
| Feb 23, 2012 at 13:09 | comment | added | Daniel James Bryars | I like sommarskog's suggestions, definitely will help avoid bugs. If they did implement a Strict option, then they could also reevaluate all the "Strict ON" sprocs when there is a table change to see if it breaks existing sprocs - obviously you would need then to have a "logical transaction on DDL" so you can then change the Table and Sprocs as one unit. | |
| Feb 23, 2012 at 13:09 | comment | added | Daniel James Bryars | Good idea about the dynamic SQL. I've got the same problem for Triggers, Indexes, Views, Sprocs, and Functions. But I've changed the code so that it just makes changes to the Tables, then Indexes, then Triggers, then functions, then sprocs. | |
| Feb 21, 2012 at 1:37 | comment | added | Aaron Bertrand | You can get around it of course by stuffing your stored procedures full of dynamic SQL - but I can't imagine generating your script to handle schema changes then stored procedures would be all that difficult. There aren't too many any options exposed to dictate how deferred name resolution works. The only proposal on the books that I know of, or at least that I can infer they're interested in entertaining, is actually the other way - making it MORE strict - see sommarskog.se/strict_checks.html). | |
| Feb 21, 2012 at 1:29 | comment | added | Daniel James Bryars | I'm pursing that option in the code now. The way the SQL is generated is fairly complicated (that was a simple example) but it's looking like it's not going to be as much as a PITA as I thought. | |
| Feb 21, 2012 at 0:47 | comment | added | Aaron Bertrand | Can't you just process all schema changes before creating/altering any procedures? Why must the procedure exist before the table is correct? | |
| Feb 20, 2012 at 23:48 | history | tweeted | twitter.com/#!/StackDBAs/status/171743078040866816 | ||
| Feb 20, 2012 at 23:11 | history | asked | Daniel James Bryars | CC BY-SA 3.0 |