stored procedures and qa

13 May 2005 7:14 PM

Claude Hebert

example:
table1 has column1, column2, column3
i create a script to make a stored procedure in query analyzer:

create proc test1 as
    alter table to add column4
    update table1 to set column4 = to something
    update table1 to set other columns to something
    alter table to drop column4
go

1) execute each internal line seperately, each runs without error

2) execute script to create the proc, ERROR cause it checks each command
before creating the proc and since the second line sets column4 to
something, and column4 is not there right now, its an invalid command and
the proc isnt created

is there any set directive or anything to prevent this behaviour

13 May 2005 7:30 PM

Jens Süßmeyer

No matter if that produre makes sense but here is the correct syntax:

Show quote

"Claude Hebert" <cheb***@rvmags.com> schrieb im Newsbeitrag
news:%233smBA$VFHA.2684@TK2MSFTNGP09.phx.gbl...
> example:
> table1 has column1, column2, column3
> i create a script to make a stored procedure in query analyzer:
>
> create proc test1 as
>    alter table to add column4
>    update table1 to set column4 = to something
>    update table1 to set other columns to something
>    alter table to drop column4
> go
>
>
>
> 1) execute each internal line seperately, each runs without error
>
> 2) execute script to create the proc, ERROR cause it checks each command
> before creating the proc and since the second line sets column4 to
> something, and column4 is not there right now, its an invalid command and
> the proc isnt created
>
> is there any set directive or anything to prevent this behaviour
>
>

13 May 2005 7:30 PM

Jens Süßmeyer

No matter if that procedure makes sense, but just for the syntax:

CREATE TABLE TABLE2
(
Column1 INT,
Column2 INT,
Column3 INT,
Column4 INT
)

CREATE PROC TEST2
AS
BEGIN
    alter table TABLE2 add COlumn5 INT
    update TABLE2 to set column4 = 'Something'
    update TABLE2 to set COlumn5 = 'Something'
    alter table TABLE2 DROP COLUMN Column5
END

HTH, Jens SUessmeyer.

---
http://www.sqlserver2005.de
---
Show quote

"Claude Hebert" <cheb***@rvmags.com> schrieb im Newsbeitrag
news:%233smBA$VFHA.2684@TK2MSFTNGP09.phx.gbl...
> example:
> table1 has column1, column2, column3
> i create a script to make a stored procedure in query analyzer:
>
> create proc test1 as
>    alter table to add column4
>    update table1 to set column4 = to something
>    update table1 to set other columns to something
>    alter table to drop column4
> go
>
>
>
> 1) execute each internal line seperately, each runs without error
>
> 2) execute script to create the proc, ERROR cause it checks each command
> before creating the proc and since the second line sets column4 to
> something, and column4 is not there right now, its an invalid command and
> the proc isnt created
>
> is there any set directive or anything to prevent this behaviour
>
>

13 May 2005 8:12 PM

Claude Hebert

THIS RUNS:

************* start
IF OBJECT_ID('dbo.test1') IS NOT NULL DROP PROCEDURE dbo.test1
go

IF OBJECT_ID('dbo.test2') IS NOT NULL DROP TABLE dbo.test2
GO

create table test2 (
col1 int null,
col2 int null,
col3 int null)

insert into test2 (col1, col2, col3)
select id, status, version
from sysobjects

alter table Test2 add ColumnTest Int Null
go

update test2 set ColumnTest = 1 where ColumnTest IS NULL

alter table Test2 drop column ColumnTest
go
****************** end
as you suggested, this: (DOESNT RUN)

************** start
IF OBJECT_ID('dbo.test1') IS NOT NULL DROP PROCEDURE dbo.test1
go

IF OBJECT_ID('dbo.test2') IS NOT NULL DROP TABLE dbo.test2
GO

create table test2 (
col1 int null,
col2 int null,
col3 int null)

insert into test2 (col1, col2, col3)
select id, status, version
from sysobjects
go

create proc test1 as
begin
alter table Test2 add ColumnTest Int Null
update test2 set ColumnTest = 1 where ColumnTest IS NULL
alter table Test2 drop column ColumnTest
end
go
************* end

returns Invalid column name 'ColumnTest' when you try and execute it

13 May 2005 9:28 PM

Jens Süßmeyer

Yeah, ok but that wont work. Server applies deferred name resolution just on
tables, not on
columns. If all tables are know at creation time SQL Server will also check
the column presence, buit if the objects are not there at creation time Sql
Server will only perform a object resolution and check at runtime, try that
withtout changing the CreationName of the table in the last query:

CREATE PROC TEST2
AS
BEGIN
    alter table TABLE3 add COlumn5 INT
    update TABLE3 set column4 = 'Something'
    update TABLE3 set COlumn5 = 'Something'
    alter table TABLE3 DROP COLUMN Column5
END

That will work, because Table3 ist not known.

HTH, Jens SUessmeyer.

---
http://www.sqlserver2005.de
---

Show quote

"Claude Hebert" <cheb***@rvmags.com> schrieb im Newsbeitrag
news:ueXELg$VFHA.228@TK2MSFTNGP12.phx.gbl...
> THIS RUNS:
>
> ************* start
> IF OBJECT_ID('dbo.test1') IS NOT NULL DROP PROCEDURE dbo.test1
> go
>
> IF OBJECT_ID('dbo.test2') IS NOT NULL DROP TABLE dbo.test2
> GO
>
> create table test2 (
> col1 int null,
> col2 int null,
> col3 int null)
>
> insert into test2 (col1, col2, col3)
> select id, status, version
> from sysobjects
>
> alter table Test2 add ColumnTest Int Null
> go
>
> update test2 set ColumnTest = 1 where ColumnTest IS NULL
>
> alter table Test2 drop column ColumnTest
> go
> ****************** end
> as you suggested, this: (DOESNT RUN)
>
> ************** start
> IF OBJECT_ID('dbo.test1') IS NOT NULL DROP PROCEDURE dbo.test1
> go
>
> IF OBJECT_ID('dbo.test2') IS NOT NULL DROP TABLE dbo.test2
> GO
>
> create table test2 (
> col1 int null,
> col2 int null,
> col3 int null)
>
> insert into test2 (col1, col2, col3)
> select id, status, version
> from sysobjects
> go
>
> create proc test1 as
> begin
> alter table Test2 add ColumnTest Int Null
> update test2 set ColumnTest = 1 where ColumnTest IS NULL
> alter table Test2 drop column ColumnTest
> end
> go
> ************* end
>
> returns Invalid column name 'ColumnTest' when you try and execute it
>
>

13 May 2005 8:18 PM

Claude Hebert

and i know moving the commands into EXEC() statements will work but theres
just to many, and initially didnt want to mess with the tables

thanks anyway...