DDL and DML in a block - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DDL and DML in a block

Author

Topic

ak4141
Starting Member

4 Posts

Posted - 2004-05-18 : 16:25:37

I have created a t-sql block and have both DDL and DML within this block. The DML references the new objects created with the DDL.
When SQL Server compiles the block it sees that the DML references invalid objects so it fails and exits the block.

In Oracle, there is a PL/SQL command called 'EXECUTE IMMEDIATE' that allows DDL and DML to reside within the same block.

Does anyone know of the equivalent in SQL Server?
Thanks in advance!
ak

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-18 : 16:41:04

You need to use GO after the DDL statements.

Tara

ak4141
Starting Member

4 Posts

Posted - 2004-05-18 : 16:42:41

The problem that I've run into with the GO stmt is that it exits out of the block and no longer let me hold the condition that allowed me to run it in the first place

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-18 : 16:44:22

Could you post the code?

Tara

ak4141
Starting Member

4 Posts

Posted - 2004-05-18 : 16:49:27

HERE IS THE CODE:
BEGIN
DECLARE @inDBChange varchar(20), @inBuild numeric(8,3), @inChangeRequest numeric(10), @inDescription varchar(255), @inCard numeric(10),@inCreatedBy varchar(20), @inRequestedBy varchar(20)
SET @inDBChange ='2004-05-11-1-10-001'
SET @inBuild = 1608.5
SET @inChangeRequest=17792
SET @inCard = 2410
set @inCreatedBy ='DBAUSER'
SET @inRequestedBy = 'DEVELOPER'
SET @inDescription = 'Renaming table Glextraction to Glexport'
IF (NOT EXISTS(select DBCHANGE FROM profdbupdatehistory where dbchange = @inDBChange) AND (Exists(select * from profversion where @inBuild <= BUILD)))
BEGIN

PRINT 'Script #' + @inDBChange

CREATE TABLE dbo.GLEXPORT
(
GLEXPORTID numeric(18, 0) NOT NULL,
PLATINUMHEADERID numeric(18, 0) NULL,
EXPORTCODE varchar(40) NOT NULL,
BOOKSETID numeric(18, 0) NOT NULL,
EXPORTTHROUGHDATE datetime NOT NULL,
EXPORTDATE datetime NOT NULL,
ISREVERSED numeric(1, 0) NOT NULL,
CONTROLTOTAL numeric(25, 7) NOT NULL,
CONTROLTOTALCC varchar(3) NOT NULL,
MAPICSHEADERID numeric(18, 0) NULL,
UNITOFWORKID numeric(18, 0) NOT NULL,
CREATEDBY varchar(40) NOT NULL,
CREATED datetime NOT NULL,
MODIFIEDBY varchar(40) NOT NULL,
LASTMODIFIED datetime NOT NULL
)

ALTER TABLE dbo.GLEXPORT ADD CONSTRAINT DF_GLEXPORT_ISREVERSED DEFAULT 0 FOR ISREVERSED

INSERT INTO GLEXPORT (GLEXPORTID,PLATINUMHEADERID,EXPORTCODE,
BOOKSETID,EXPORTTHROUGHDATE,EXPORTDATE,ISREVERSED,
CONTROLTOTAL,CONTROLTOTALCC,MAPICSHEADERID,UNITOFWORKID,
CREATEDBY,CREATED,MODIFIEDBY,LASTMODIFIED)
SELECT GLEXTRACTIONID,PLATINUMHEADERID, EXTRACTIONCODE, BOOKSETID, EXTRACTTHROUGHDATE, EXTRACTDATE, ISREVERSED,
CONTROLTOTAL,CONTROLTOTALCC,MAPICSHEADERID,UNITOFWORKID,CREATEDBY, CREATED, MODIFIEDBY, LASTMODIFIED FROM GLEXTRACTION

ALTER TABLE dbo.GLEXPORT ADD CONSTRAINT
PK_glexport PRIMARY KEY CLUSTERED
(
GLEXPORTID
)

ALTER TABLE dbo.GLEXPORT ADD CONSTRAINT
FK_GLEXPORT_MAPICSHEADER FOREIGN KEY
(
MAPICSHEADERID
) REFERENCES dbo.MAPICSHEADER
(
MAPICSHEADERID
)

ALTER TABLE dbo.GLEXPORT ADD CONSTRAINT
FK_GLEXPORT_PLATINUMDETAIL FOREIGN KEY
(
PLATINUMHEADERID
) REFERENCES dbo.PLATINUMDETAIL
(
PLATINUMDETAILID
)

ALTER TABLE dbo.GLEXPORT ADD CONSTRAINT
FK_GLEXPORT_CURRENCY FOREIGN KEY
(
CONTROLTOTALCC
) REFERENCES dbo.CURRENCY
(
CURRENCYCODE
)

ALTER TABLE dbo.GLEXPORT ADD CONSTRAINT
FK_GLEXPORT_BOOKSET FOREIGN KEY
(
BOOKSETID
) REFERENCES dbo.BOOKSET
(
BOOKSETID
)

ALTER TABLE dbo.ACCOUNTINGTRANSACTION DROP CONSTRAINT FK_ACCTTXN_GLEXTACTION
ALTER TABLE dbo.ACCOUNTINGTRANSACTION ADD GLEXPORTID numeric(18, 0) NULL

UPDATE ACCOUNTINGTRANSACTION SET GLEXPORTID = GLEXTRACTIONID

ALTER TABLE dbo.ACCOUNTINGTRANSACTION ADD CONSTRAINT
FK_ACCTINGTRANSACT_GLEXPORT FOREIGN KEY
(
GLEXPORTID
) REFERENCES dbo.GLEXPORT
(
GLEXPORTID
)

exec dbo.applyUpdateInsertInto1 @inDBChange,@inBuild,@inChangeRequest,@inDescription,@inCard, @inCreatedBy,@inRequestedBy
END
END
go

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-18 : 16:56:40

The server will generate the query plan for the batch. It will assume for that plan that static objects are static - it will only cater for creations (but not alters) of temp tables.
Therefore your inserts must be in a separate batch to the creates. You can do this by a "go" or by executing them as dynamic sql or in an SP .
You are going to meet a lot of problems if you try to do things the way are though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ak4141
Starting Member

4 Posts

Posted - 2004-05-18 : 17:05:22

Thanks NR,
I guess I'm confused by the comment 'You are going to meet a lot of problems if you try to do things the way are though'.
I've been doing this is Oracle for a while and it has the EXECUTE IMMEDIATE command and I haven't had any problems.
I take it that SQL Server does not have the equivalent?
Is there an extended stored procedure that allows this functionality? As you can see by the if stmt (its usage is designed to meet our versioning in our development env), a GO command really isn't possible. If I must I will separate into different blocks.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-18 : 17:13:41

Yep sql server and oracle are not the same.

I think the equivalent is t-sql would be dynamic sql. Put the inserts into a string and execute them.

Another way is to create a temporary SP and execute it with the inserts - but that won't be much different.

I do this sort of thing by creating scripts to make the changes and executing via osql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources