November 10, 2009 at 8:07 am
Hi guys,
I would like to know how can I do some thing like to get errors if any while creating stored procedures.
Like for an example, I do not have the table "XYZ" in my database and I am trying to create this stored procedure as follows
CREATE PROCEDURE mysp as
begin
select * from XYZ
end
And I successfully created this stored procedure and get error while executing this stored procedure
Msg 208, Level 16, State 1, Procedure mysp, Line 3
Invalid object name 'XYZ'.
I would like to get this above error while creating the stored procedure it self,
Please advice.
Thanks
Manish
- Manish
November 10, 2009 at 8:15 am
You sure that you are using 2008.
Because in SSMS 2008 it gives an error if you type a table name that doesn't exists.
-Vikas Bindra
November 10, 2009 at 8:54 am
Hi Vikas,
Yes, I am using Sql Server 2008 Version 10.0.1600.22
- Manish
November 10, 2009 at 8:56 am
vikas bindra (11/10/2009)
You sure that you are using 2008.Because in SSMS 2008 it gives an error if you type a table name that doesn't exists.
not true.
when it comes to compiling stored procedures, even on 2008, the late binding mechanism allows you to create a proc that references tables that do not exist. that is why people will tell you that sysdepends is unreliable...because procedures could be missing dependancies at creation time.
when you RUN the proc, and the table does not exist, you'd get an error , but not at compile time.
I do not know of anyway to force the proc to fail if the table doesn't exist yet; it's a feature of procedures
Lowell
November 10, 2009 at 1:55 pm
SET NOEXEC ON
After that, you can execute you procedure. The procedure will compile (and perform the object and syntax checks you seek), but will not execute.
If you wish to execute commands later on the same connection, SET NOEXEC OFF.
CREATE PROC dbo.TestProc
AS
SELECT * FROM dbo.ThisDoesNotExist
GO
-- Set the connection in compile-but-not-execute mode
SET NOEXEC ON
GO
-- Now test the procedure. This should throw an error.
-- If the procedure compiles, it will not be executed
EXEC dbo.TestProc
GO
-- in case you want to run more T-SQL...
SET NOEXEC OFF
GO
Eddie Wuerch
MCM: SQL
October 17, 2011 at 9:39 am
SET NOEXEC ON doesn't work for me on Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
Any thoughts?
I'm testing for an upgrade, added a dummy table to a stored proc and tried the above, no errors.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 17, 2011 at 9:50 am
based ont he exact example Eddie posted above, his example worked and compiled just fine on my machine.
NO EXEC didn't raise any errors at all;
Waht was your exact error? did you use code that was different froma bove?
this thread is old, so i think only those who subscribed to it will see it, we might want to start a new thread.
my @@version was:
Microsoft SQL Server 2008 (SP2) -
10.0.4000.0 (X64)
Sep 16 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64>
(Build 7600: )
Lowell
October 17, 2011 at 10:00 am
I only wanted to talk to you anyway 😛
Thought we were *looking* for it to generate errors, so we know which procedures have syntax issues? Anyway, found a different post where you and Paul talked about Deferred Name Resolution, and how an invalid object doesn't throw an error, while an invalid column will.
Think I'm going to run my test using SET NOEXEC ON for invalid columns, then parse through sys.syscomments to find completely invalid objects in the joins.
Thanks for the response.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 17, 2011 at 10:20 am
Keep in mind that this will still not get you what you are looking for, I think. The procedure will still compile and will be created, THEN you will execute it without "really" executing it and see the same errors you would if you were "really" executing it. Can I ask why you are interested in this? Is it to prevent the creation of an invalid stored procedure? If that is the case, I suggest not letting people who don't know what they are doing create procedures 🙂 Otherwise you cannot prevent it.
Thanks,
Jared
Jared
CE - Microsoft
October 17, 2011 at 10:26 am
jared-709193 (10/17/2011)
Keep in mind that this will still not get you what you are looking for, I think. The procedure will still compile and will be created, THEN you will execute it without "really" executing it and see the same errors you would if you were "really" executing it. Can I ask why you are interested in this? Is it to prevent the creation of an invalid stored procedure? If that is the case, I suggest not letting people who don't know what they are doing create procedures 🙂 Otherwise you cannot prevent it.Thanks,
Jared
system upgrade, schema changes, so I'm testing the stored procedures to find which need modified.
Nice suggestion, but then I wouldn't be allowing myself to do anything...:hehe:
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 17, 2011 at 10:30 am
jcrawf02 (10/17/2011)
jared-709193 (10/17/2011)
Keep in mind that this will still not get you what you are looking for, I think. The procedure will still compile and will be created, THEN you will execute it without "really" executing it and see the same errors you would if you were "really" executing it. Can I ask why you are interested in this? Is it to prevent the creation of an invalid stored procedure? If that is the case, I suggest not letting people who don't know what they are doing create procedures 🙂 Otherwise you cannot prevent it.Thanks,
Jared
system upgrade, schema changes, so I'm testing the stored procedures to find which need modified.
Nice suggestion, but then I wouldn't be allowing myself to do anything...:hehe:
Well... Another option is to make sure intellisense is enabled and simply look at the sp for any red underlines. Simple yet effective!
Jared
Jared
CE - Microsoft
October 26, 2011 at 4:18 pm
I just ran into this issue today when one of my clients asked me why this was happening. I'd never noticed this before and it really struck me as odd that a procedure would compile with "invalid" tables, but not columns.
Red-Gate makes a tool (SQL Prompt) that integrates with SSMS and allows you to quickly identify invalid objects. I guess I now need to make this part of my standard operating procedure when making any table/procedure updates.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply