October 1, 2009 at 6:04 pm
Hi,
Just wondering if there is a way to force SQL Server to skip checking if a table or a column of a table, which a used by the SP, exist when creating a Stored Procedure.
Thanks.
October 1, 2009 at 6:45 pm
You could convert the sql statement inside your proc to be dynamic SQL. That way the compiler won't check.
October 1, 2009 at 6:47 pm
Do not know why you would want to create an SP and not know if it would function, but yes it can be done for example:
CREATE PROCEDURE Dbo.Garbage
AS
SELECT * FROM Dbo.NoneExisting
WHERE Col1 > 0
Just click on the execute button to create the procedure, and it is, providing the syntax is correct. Now invoking the procedure of course will result in errors since in my DB the table named NoneExisting does not exist.
October 1, 2009 at 6:59 pm
bitbucket-25253 (10/1/2009)
Do not know why you would want to create an SP and not know if it would function, but yes it can be done for example:
CREATE PROCEDURE Dbo.GarbageAS
SELECT * FROM Dbo.NoneExisting
WHERE Col1 > 0
Just click on the execute button to create the procedure, and it is, providing the syntax is correct. Now invoking the procedure of course will result in errors since in my DB the table named NoneExisting does not exist.
Nope, this one does not work. It errors that some columns are missing.
The reason I want this is because I need to run an import script which runs better when indexed views are not there yet, which relies on a stored procedure script (one of the stored procedures used in the import script.) Some of the other stored procedures rely on the views, which should not be there yet for the import script to work. I understand that I could split the stored procedures script into the part used in the import script and the rest and break the circle, but I do not want to do this.
October 1, 2009 at 7:01 pm
philip-seamark (10/1/2009)
You could convert the sql statement inside your proc to be dynamic SQL. That way the compiler won't check.
I guess this would work, but it involves to many changes to the scripts.
I thought there would be some sort of option which you set in the begining of the script and then run the script creating stored procedures safely.
October 2, 2009 at 12:37 am
Roust_m (10/1/2009)
bitbucket-25253 (10/1/2009)
Do not know why you would want to create an SP and not know if it would function, but yes it can be done for example:
CREATE PROCEDURE Dbo.GarbageAS
SELECT * FROM Dbo.NoneExisting
WHERE Col1 > 0
Just click on the execute button to create the procedure, and it is, providing the syntax is correct. Now invoking the procedure of course will result in errors since in my DB the table named NoneExisting does not exist.
Nope, this one does not work. It errors that some columns are missing.
Just out of curiosity!, it should compile just fine and if you are executing then only it would throw an error!
---------------------------------------------------------------------------------
October 2, 2009 at 7:25 am
PP-564103 (10/2/2009)
Roust_m (10/1/2009)
bitbucket-25253 (10/1/2009)
Do not know why you would want to create an SP and not know if it would function, but yes it can be done for example:
CREATE PROCEDURE Dbo.GarbageAS
SELECT * FROM Dbo.NoneExisting
WHERE Col1 > 0
Just click on the execute button to create the procedure, and it is, providing the syntax is correct. Now invoking the procedure of course will result in errors since in my DB the table named NoneExisting does not exist.
Nope, this one does not work. It errors that some columns are missing.
Just out of curiosity!, it should compile just fine and if you are executing then only it would throw an error!
These will compile fine if the entire table does not exist. They will not compile if the table exists but the column you're referencing does not. It will error. Try it.
October 2, 2009 at 8:32 am
Garadin
These will compile fine if the entire table does not exist. They will not compile if the table exists but the column you're referencing does not. It will error. Try it.
Tried in using 2005 with the table existing ... works fine for me; Now remember I said just click on the Execute button which does not parse the procedure, it just saves it...
October 2, 2009 at 10:03 am
October 2, 2009 at 10:48 am
This fails on my 2K8 server on 80/90/100 Compatibility levels. I'm honestly not sure how you have it working on yours.
--CREATE DATABASE Test
--GO
ALTER DATABASE Test SET COMPATIBILITY_LEVEL = 100
GO
USE Test
CREATE TABLE #1(
aint)
GO
CREATE PROCEDURE sethtest1
AS
SELECT a,b
FROM #1
GO
DROP TABLE #1
Error:
Msg 207, Level 16, State 1, Procedure sethtest1, Line 5
Invalid column name 'b'.
Looking into this a bit resulted in the following:
http://msdn.microsoft.com/en-us/library/ms190686.aspx
When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the sys.sql_modules catalog view.When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the sys.sql_modules catalog view and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because table objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.
Note:
Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created.For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.
October 2, 2009 at 4:23 pm
Garadin
These were my test conditions using Developer version of SQL Server 2008 Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )
/
ALTER DATABASE Test
SET COMPATIBILITY_LEVEL = 100
CREATE TABLE Dbo.NonExisting
(ABC VARCHAR(10))
CREATE PROCEDURE Dbo.Garbage
AS
SELECT * FROM Dbo.NoneExisting
WHERE Col1 > 0
CREATE PROCEDURE Dbo.Garbage2
AS
SELECT ABC FROM Dbo.NoneExisting
WHERE Col1 > 0
DROP TABLE Dbo.NonExisting
DROP PROCEDURE Dbo.Garbage
DROP PROCEDURE Dbo.Garbage2
Message for each of the above was
Command(s) completed successfully.
Now I am as puzzled as you are as to what is the difference / why there is a difference. Could it have been an error in the pre-release SSMS and fixed in the production release? Not likely.
October 2, 2009 at 4:37 pm
Aside from the discussion about how to create stored procs that reference non-existent objects - isn't the best solution for the OP to keep the views, but drop the indexes from them, rather than drop the views entirely? Or am I missing something?
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
October 2, 2009 at 4:43 pm
The original question by the OP was
Just wondering if there is a way to force SQL Server to skip checking if a table or a column of a table, which a used by the SP, exist when creating a Stored Procedure.
It then became more and more complex, and the discussion of the original question has just continued on the basis that others jumped in with solutions to the more complex question.
October 2, 2009 at 4:49 pm
Yeah, but you said you didn't know why the OP would want to do that, and he clarified:
...The reason I want this is because I need to run an import script which runs better when indexed views are not there yet, which relies on a stored procedure script...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
October 2, 2009 at 4:51 pm
bitbucket-25253 (10/2/2009)
GaradinThese were my test conditions using Developer version of SQL Server 2008 Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )
/ALTER DATABASE Test
SET COMPATIBILITY_LEVEL = 100
CREATE TABLE Dbo.NonExisting
(ABC VARCHAR(10))
CREATE PROCEDURE Dbo.Garbage
AS
SELECT * FROM Dbo.NoneExisting
WHERE Col1 > 0
CREATE PROCEDURE Dbo.Garbage2
AS
SELECT ABC FROM Dbo.NoneExisting
WHERE Col1 > 0
DROP TABLE Dbo.NonExisting
DROP PROCEDURE Dbo.Garbage
DROP PROCEDURE Dbo.Garbage2
Message for each of the above was
Command(s) completed successfully.
Now I am as puzzled as you are as to what is the difference / why there is a difference. Could it have been an error in the pre-release SSMS and fixed in the production release? Not likely.
If only your SSMS had a spell check! 😉
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply