There are quite a few statements, principally revolving
around CREATE that have to be in their own batch - CREATE
PROC for example.

So if I were to need to create a procedure based on some
logic, how could I conditionally call a batch?

The book makes mention of using EXEC to run batches but I
cant find any reference to how this is done.

I have found a way of doing it but am not sure that its
anything like a good way:-

IF EXISTS (SELECT name FROM sysobjects where name
= 'up_invcount')
PRINT 'This procedure already exists'
ELSE
BEGIN
PRINT 'Create proc'
CREATE PROC up_invcount
WITH recompile
AS
SELECT c.customername, count(i.[invoice number])
FROM customers c JOIN [invoice header ] i ON
(c.customerid = i.customerid)
GROUP BY c.customername
ORDER BY c.customername
END
GO

This will give an error as the CREATE PROC is not within
its own batch

However if we write the CREATE PROC (in blue) as a sql
file - "C:\create_proc.sql" and run the batch:

IF EXISTS (SELECT name FROM sysobjects where name
= 'up_invcount')
PRINT 'This procedure already exists'
ELSE
BEGIN
PRINT 'Create proc'
EXECUTE master.dbo.xp_cmdshell 'osql -Swin2k\sql2000 -Usa -
Pxxxxxx -dss2Idata -iC:\create_proc.sql', No_Output
END
GO

The batch is run externally by osql using the extended
procedure xp_cmdshell.

As I say, there must be a better way

One alternative, if the condition for creating the proc is that it doesn't
exist, is to just drop it if it exists, and then create it unconditionally.

IF EXISTS (.....)
DROP PROCEDURE ...
GO
CREATE PROC up_invcount
GO

Also, EXEC to run batches is documented in Books online under EXECUTE.

EXEC('CREATE PROC up_invcount AS ..... ')

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


"bob" <back (AT) hawaii (DOT) com> wrote