| Author |
Topic |
|
jed992
Starting Member
5 Posts |
 Posted - 2011-11-14 : 16:12:23
|
| Hello,I've changed the names of a few db tables. If I run an associated stored procedure before modifying the table name, I would expect to get error messages but I'm not. SSMS just says "Command Executed Successfully". Is this correct behavior? I don't think it used to work this way in 2005.Thank you,Jim |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jed992
Starting Member
5 Posts |
Posted - 2011-11-14 : 16:49:06
|
| I have a simple stored proc:ALTER Procedure Position_GetAllPositionsASDECLARE @RC INTSET @RC=0SELECT [PositionID] ,[JobCode] ,[Title] ,[IsActive] ,[CreatedOn] ,[CreatedBy]FROM PositionRETURN @RC If I modify "FROM Position" to, for example "FROM Position1234" (a non-existent table), the stored procedure still runs without any errors or warnings. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-11-14 : 16:59:28
|
| The stored procedure will ALTER without error. Run it (EXEC Position_GetAllPositions) and you should get an error--Gail ShawSQL Server MVP |
|
|
|
jed992
Starting Member
5 Posts |
Posted - 2011-11-14 : 19:21:32
|
quote:
Originally posted by GilaMonster
The stored procedure will ALTER without error. Run it (EXEC Position_GetAllPositions) and you should get an error--Gail ShawSQL Server MVP
Actually my stored proc first does a DROP, then a CREATE. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jed992
Starting Member
5 Posts |
Posted - 2011-11-15 : 09:18:04
|
| I know that running the sproc with the EXEC command will give me an error. The problem is this: say I had a very long, complicated stored procedure which does a SELECT from a table called "Position." For some reason, I have to rename the "Position" table to "Position1234." If I edit the sproc to change all of the references of "Position" to "Position1234", but I miss one of them, I would expect the sproc to fail when I recompile it. I realize that using the EXEC command would let me know this. But what if the sproc takes 10 parameters? Then it's a lot more work to figure this out. And what if I have 50 sprocs to update. It will take quite a while to update everything.I asked another developer to try this out in SSMS 2005. If you try to run a sproc with an invalid table name, SSMS throws an error. I was hoping that there was an option available in 2008 that |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-11-15 : 14:00:40
|
| You are still missing the point:This is called deferred name resolution - and that is normal behavior. You can create a stored procedure that references a table that has not been created yet. You are also confusing the terminology - you do not 'Run' a stored procedure. You either EXECUTE, CREATE, DROP or ALTER a stored procedure.When your coworker 'ran' the stored procedure - did he EXECUTE it or ALTER it? My guess is that your coworker executed the procedure, and hence - it erred.And finally, if you are modifying stored procedures and expect to be able to just alter it and move on - then how are you going to know your changes did not cause additional issues? You should always be testing your changes to validate them, even if there are 50 procedures that need to be modified. For those procedures that require multiple parameters, you need to create a test case and store that with the procedure - so you can execute it every time with the same values and validate the results.Jeff |
|
|
|
jed992
Starting Member
5 Posts |
Posted - 2011-11-15 : 15:32:45
|
quote:
Originally posted by jeffw8713
This is called deferred name resolution
Ok, "deferred name resolution" is the answer I needed. I don't believe that 2005 worked this way. My coworker did in fact EXECUTE the sproc, which resulted in error. And, Jeff, thanks for the tip about writing test cases. I guess in your laid-back world of programming, you have time to write a test case for each sproc, but I work in a real programming environment with business owners who want everything done by yesterday. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-11-15 : 16:07:08
|
quote:
Originally posted by jed992
I don't believe that 2005 worked this way. My coworker did in fact EXECUTE the sproc, which resulted in error.
2005 did, and 2000 and 7 and probably earlier versions too. It's not a recent thing added.quote:
And, Jeff, thanks for the tip about writing test cases. I guess in your laid-back world of programming, you have time to write a test case for each sproc, but I work in a real programming environment with business owners who want everything done by yesterday.
Wow. Seriously?Personally, in the high-risk, high-pressure, high-speed business world test cases and proper testing is far more important than in hobby code. In a high-pressure environment I want to know immediately if a change I made broke some other part of the system (that was supposed to be unrelated), and proper test cases (used properly) will tell me that before the business user comes down screaming blue murder p.s. I've also worked in those high-pressure environments. The more high pressure, the more I want good test cases.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-16 : 03:49:03
|
quote:
Originally posted by jed992
And, Jeff, thanks for the tip about writing test cases. I guess in your laid-back world of programming, you have time to write a test case for each sproc, but I work in a real programming environment with business owners who want everything done by yesterday.
Hahahaha ... hand your notice in and go and work somewhere else.If your boss requires you to write untested code then s/he will not think twice about firing you for any bugs you introduce into the system.Testing software before deploying it is much cheaper than fixing bugs once it is live (and its cheaper still if you fix a design issue, at the design stage, rather than the build stage). |
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-11-16 : 04:17:13
|
| jed992 - If you have no change process (and it sounds like you don't) I would suggest reading up on ITIL.----------------------------Junior DBA learning the ropes |
|
|
|
|
Running Stored Procedures