Palmer-Creating Stored Procedures or Triggers Inside Stored Procedures

[MynameisLogan]

First of all thanks to Palmer for his reply about my previous question on creating stored procedures inside stored procedures.

Using the Execute ('SQL Statements') to create stored procedures inside stored procedures was my strategy also. The problem is that the main stored procedure will reside in a master database and the procedures and triggers that it will create, will be created on other databases in the same SQL Server.

In the creation of triggers I can not use a direct reference to the database name, so I have to use another method. What I tried is this:

/***************************************************************************/
/* Creation of Triggers */
/***************************************************************************/
DECLARE @CTrigProc varchar(4000)

/****** Object: Trigger dbo.trgIDTtpeUpdate Script Date: 2/15/01 12:14:58 PM ******/
SELECT @CTrigProc = "Use Praemis_Master" + Char(13) + Char(10) +
"CREATE TRIGGER trgIDTtpeUpdate ON IDType FOR UPDATE AS" + Char(13) +
"SET NOCOUNT ON" + Char(13) +
"/* * CASCADE UPDATES TO 'IDType' */" + Char(13) +
"IF UPDATE(IDocType)" + Char(13) +
" BEGIN" + Char(13) +
" UPDATE Student" + Char(13) +
" SET Student.IDocType = inserted.IDocType" + Char(13) +
" FROM Student, deleted, inserted" + Char(13) +
" WHERE deleted.IDocType = Student.IDocType" + Char(13) +
" END"
EXECUTE (@CTrigProc)


The error I get is :

Server: Msg 111, Level 15, State 1, Line 2
'CREATE TRIGGER' must be the first statement in a query batch.
Server: Msg 140, Level 15, State 1, Line 2
Can only use IF UPDATE within a CREATE TRIGGER statement.

/***************************************************/

Any reference or solution will be useful
Thanks

 

[Palmer]

Sorry, been at meetings all day.

My thoughts on this are as follows.

The only way I can see of doing this is to make use of the isql command line utility by shelling out within a stored procedure using xp_cmdshell as follows:


CREATE PROC palmproc
AS
EXEC master..xp_cmdshell 'isql /U /P /e /n /o /d slvrlkps /Q "create procedure newproc as select * from information_schema.columns"'

GO


Where the /d represents the database name - in this case slvrlkps

This may be overly simplistics the the principle may be applied to your problem.

Hope this helps.

 

[ColinM]

I think you could just add GO i.e.

DECLARE @CR CHAR(2)
SET @CR = CHAR(13) + CHAR(10)

SELECT @CTrigProc = "Use Praemis_Master" + @CR + "GO" + @CR

This will separate the SQL into different batches.