compile SP after creation?

Hello
Is it possible to force SQL Server to compile stored procedures after
creation? I mean, when I execute something like:
CREATE PROC SP1 AS
select * from X
where table X does not exist, I want to get error or warning. Now the proc
is created and the error is thrown only when it is executed. We have more
then 1500 stored procedures in the database now scripted out into sql
scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
are valid after developers updates but by simply running the script to creat
e
DB and all its content does not produce any warning/error. How can we
accomplish that?
Thanks
eXavierHi
i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
NULL then object does not exist and your error will be thrown
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
I'm not sure if I understand what you mean. Do you mean to write some sql
script parser, extract all table names from all stored procedures and then
call your IF check? This is not acceptable for us. In fact we want the serve
r
to compile procedures to get eventual errors.. SP is compiled when executed
first but we cannot automatically execute them all as they have different
parameters..
Any other idea?
Thanks
eXavier
"Uri Dimant" wrote:

> Hi
> i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
> NULL then object does not exist and your error will be thrown
>
>
> "eXavier" <eXavier@.community.nospam> wrote in message
> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
>
>|||Specify WITH RECOMPILE in your stored procedure. The procedure will not be
cached and will recompile at runtime.
--
MG
"eXavier" wrote:

> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..
)
> are valid after developers updates but by simply running the script to cre
ate
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
WITH RECOMPILE does exactly what you wrote. But it does not help in creation
time - you can still create procedure referencing non-existant tables.
The error is thrown only when the proc is executed, coming back to our
problem. (Further, if the SP compiles we want it to be cached.)
"MGeles" wrote:
[vbcol=seagreen]
> Specify WITH RECOMPILE in your stored procedure. The procedure will not b
e
> cached and will recompile at runtime.
> --
> MG
>
> "eXavier" wrote:
>|||I'm afraid you cannot do that
"eXavier" <eXavier@.community.nospam> wrote in message
news:4BBE6A41-D987-4A5C-A953-1BD71A01A972@.microsoft.com...[vbcol=seagreen]
> Hi,
> I'm not sure if I understand what you mean. Do you mean to write some sql
> script parser, extract all table names from all stored procedures and then
> call your IF check? This is not acceptable for us. In fact we want the
> server
> to compile procedures to get eventual errors.. SP is compiled when
> executed
> first but we cannot automatically execute them all as they have different
> parameters..
> Any other idea?
> Thanks
> eXavier
> "Uri Dimant" wrote:
>|||"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation?
Wayyy back in the day (SQL 6.5 I believe) this was possible, since SQL
checked for the existence of objects before creating stored procedures.
Apparently this caused a bunch of headaches, especially with database object
scripts that weren't created in the proper order, so "deferred name
resolution" was introduced. Table names are not resolved until run-time
instead of at creation time. Your best bet might be to 1) Do what Uri
suggested and explicitly check for the existence of tables yourself before
running the CREATE SP statement, or 2) Do what MS suggests below and test
your code after creation. Uri's suggestion should not be all that
difficult. Maybe a single script that tests for the existence of *all*
tables that are supposed to be in the database. You could run this script
before you try to run any SP creation scripts, and abort the mission based
on the result.
Here's what MS has to say on deferred name resolution:
"Deferred Name Resolution. Deferred name resolution allows procedure
compilation without all table references being present. Deferred name
resolution works in much the same way as the object-oriented concept of late
binding. At compile time, the compiler attempts to resolve all table names
that the procedure references. But if a table does not yet exist, the
compiler defers this name resolution until execution time.
For developers who have used temporary tables within their stored procedures
or triggers, this subtle new feature is long overdue. Although this feature
is useful, it has a side effect that many developers might initially
miss-the compiler no longer reliably catches table-name typos. Yes, you now
must test your code. This statement might sound funny at first, but if you
are not aware of deferred name resolution, you might find yourself wondering
why the compiler missed this error."
(From
http://www.microsoft.com/technet/pr...loy/migrat.mspx)|||Hi eXavier,
xyz's suggestion is reasonable. Appreciate your understanding that this
requirement is individual and actually limited by the design of SQL Server.
It is impossible for us to change the native behavior. I noticed that you
just wanted to ensure that scripts are valid after developers updates but
by simply running the script to create DB and all its content does not
produce any warning/error. You may consider to find a way from management.
For example, setup a test database environment which is same as the
development database then script a file to execute all the SPs or UDFs
that you want to check. This may require a standard process that the
developer of a SP or a UDF should provide a SQL test statement which can be
directly copied into the script file for checking at runtime. If some
errors are thrown out, please check and correct both the test database and
the development database. It is important to keep the identical environment
between the test database and the development database.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||You can catch most issues by executing the proc with SET FMTONLY ON like the
example below (passing any needed parameters as NULL values). However, some
errors can only be found by actually executing the proc.
SET FMTONLY ON
GO
EXEC dbo.SP1
GO
SET FMTONLY OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier