> I've written a TSQL procedure which adds 2 columns to 2 tables.
> One table is in the database where the TSQL procedure is written.
> The other table is in another database.
> (Alter table databasename.dbo.tablename ADD column char(6).
>
> I can not save the stored procedure since that column is "wrong".
> When I create this column first and try to store the procedure after
> creating it it is not a problem.
>
> However the procedure has no problems with the alter statement in the own
> database.
> That column does not exist either but it is no problem to save the
> procedure at that time.

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns93ECF019F25FFYazorman@127.0.0.1...
> A.M. de Jong (arnojo@wxs.nl) writes:
> > I've written a TSQL procedure which adds 2 columns to 2 tables.
> > One table is in the database where the TSQL procedure is written.
> > The other table is in another database.
> > (Alter table databasename.dbo.tablename ADD column char(6).
> >
> > I can not save the stored procedure since that column is "wrong".
> > When I create this column first and try to store the procedure after
> > creating it it is not a problem.
> >
> > However the procedure has no problems with the alter statement in the
own
> > database.
> > That column does not exist either but it is no problem to save the
> > procedure at that time.
>
> It would have helped if you had included the error message you are
> getting, and even more if you also had included the code.
>
> Without that information, I can only give a generic answer. This fails:
>
> CREATE TABLE nisse (a int NOT NULL)
> go
> CREATE PROCEDURE nisse_sp AS
> ALTER TABLE nisse ADD b datetime NULL
> SELECT b from nisse
> go
>
> Because when you create nisse_sp, nisse.b does not yet exist. SQL
> Server applies deferred name resolution on tables, but not on
> columns. If you in a stored procedure in query refer to a table
> that does not exist, SQL Server will defer checking for tables and
> columns in that query until run-time. However, if all tables
> referred to in the query exist, SQL Server also checks that all
> columns exist. Which in my opinion is a good thing.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

> I noticed allready that you are more or less correct.
> I indeed alter a table (add a column) and I want to update this column
> afterwards.
> (Since I am making a stored procedure to "deliver" a new database version)
> But I have to do this for 2 databases.
> (Same problem; different tables).
> For one of those databases it is not a problem to create this stored
> procedure.
> (That is odd is not it)
> Is it possible to create a stored procedure with no check ??