August 27, 2008 at 10:45 pm
Hi everybody!
I'm trying to create the stored procedure CrTr on database 'a'. This stored procedure must create a trigger on the table 'b' on the database 'b'.
My script looks like this:
use [a]
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[CrTr]
AS
BEGIN
SET NOCOUNT ON;
declare @cmd1 nvarchar(1000)
set @cmd1 = '
use
go
CREATE TRIGGER Tr
ON b.dbo.b
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into a.dbo.a select * from inserted
END'
print @cmd1
exec sp_executesql @cmd1
This stored procedure is created without any error. But when I execute it I get the below error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'go'.
Msg 111, Level 15, State 1, Line 3
'CREATE TRIGGER' must be the first statement in a query batch.
I do know that the CREATE TRIGGER must be the first statement in a query batch. That's why I add
'USE b'
Go
to my code.
I eagerly would be appreciated if anybody help.
Thnx
Safa
August 27, 2008 at 11:15 pm
harmonica1313 (8/27/2008)
...
set @cmd1 = '
use
go
CREATE TRIGGER Tr
...
END'
"GO" is NOT a SQL Server command. It is a client flag/command. In this case it is a flag to SSMA's Query window tend the current batch and execute and to start a new one. This has two consequences for your code:
1. You cannot include "GO" in dynamic SQL (or stored procedures), because it is not a SQL Server command, thus you cannot use GO to start a new batch in dynamic SQL or stored procedures.
2. Anytime a line begins with "GO" in a query window, it will terminate the current batch, no matter how you try to hide it: in text quotes ('...'), or in comments (/*..*/). If the line begins with "GO ", it will flag the client query window to terminate the batch.
So, in summary: you cannot use GO like this.
If you want to control batch creation and termination in stored procedures and dynamic sql, you need to nest the dynamic SQL statment: "EXEC(string)". To start a new batch, use the EXEC('...') statement which will start a new nested batch command stream inside the current one. To terminate a batch, just make sure that the string being executed dynamically, ends there. Then the nested dynamic SQL batch will terminate and return to the calling context/batch.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 7:42 am
Hi again
Thank you for your cooperation.
I believe that your solution do work; although I tried to apply your solution to my problem, I couldn't get the expected results. May you kindly help me further to find the solution?
Actually I cannot realize how to nest the commands.
Thanks again
August 28, 2008 at 8:23 am
OK, off-hand, here is how I would do it (not tested):
use [a]
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[CrTr]
AS
BEGIN
SET NOCOUNT ON;
declare @cmd1 nvarchar(1000)
set @cmd1 = '
use
EXEC (''
CREATE TRIGGER Tr
ON b.dbo.b
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into a.dbo.a select * from inserted
END'')'
print @cmd1
exec sp_executesql @cmd1
Note the need to double the quotes every time you nest inward another level.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 2, 2008 at 12:10 am
Woow
Thank you very much,
It seems that it would work, However there is still some problem with "quotes". I get the error : "Incorrect syntax near '@cmd1'." I doubled the quotes as you told, but the error is still there.
September 2, 2008 at 9:27 am
oops, sorry. It's missing the "END" statement at the end of the procedure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 2, 2008 at 8:24 pm
Alright,
thank you very much
it is solved now,
You saved me guy 😉
September 2, 2008 at 10:22 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply