August 17, 2010 at 5:47 am
August 17, 2010 at 5:49 am
Is there a question here? Your code looks like it should work, just add an EXEC to run the dynamic sQL you generated.
I do have to ask... Why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2010 at 5:57 am
Hi,
Thanks for reply
create procedure aa
as
begin
if exists (select * from sys.objects where name='bb')
begin
drop procedure bb
go
declare @a nvarchar(max)
set @a='create procedure bb as begin print ''bb'' end'
exec sp_executesql @a
end
end
after run this code i got following error message
Msg 102, Level 15, State 1, Procedure aa, Line 9
Incorrect syntax near 'bb'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'end'.
August 17, 2010 at 6:06 am
Well, the GO in the 'middle' of procedure A ends the batch. Hence leaving a begin without an end and, in the second batch an end without a begin.
GO is a batch breaker. It tells SSMS where to delimit the batches sent to SQL. So what you're telling SSMS is that the first batch it must send is
create procedure aa
as
begin
if exists (select * from sys.objects where name='bb')
begin
drop procedure bb
GO
As I'm sure you can see, that is not valid SQL.
Again, I need to ask why you want a procedure to create a procedure? What's the point?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2010 at 6:38 am
or it shud be like below
create procedure aa
as
begin
if exists (select * from sys.objects where name='bb')
begin
drop procedure bb
declare @a nvarchar(max)
set @a='create procedure bb as begin print ''bb'' end'
exec sp_executesql @a
end
end
But as Gail's suggestion.... why u want to do this??? is this some class room based question???
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply