|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Friday, January 06, 2012 5:00 AM
Points: 114,
Visits: 74
|
|
Hi, I want to create procedure within procedure
something like that
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' end
end
Thanks in Advance
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 4:07 AM
Points: 38,112,
Visits: 30,400
|
|
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 2008, MVP SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter We stand on the bridge and no one may pass
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Friday, January 06, 2012 5:00 AM
Points: 114,
Visits: 74
|
|
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'.
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 4:07 AM
Points: 38,112,
Visits: 30,400
|
|
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 2008, MVP SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter We stand on the bridge and no one may pass
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 225,
Visits: 421
|
|
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???
|
|
|
|