Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

create Procedure within Procedure Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 5:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Post #970280
Posted Tuesday, August 17, 2010 5:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 41,532, Visits: 34,449
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

Post #970283
Posted Tuesday, August 17, 2010 5:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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'.


Post #970292
Posted Tuesday, August 17, 2010 6:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 41,532, Visits: 34,449
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

Post #970302
Posted Tuesday, August 17, 2010 6:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:55 AM
Points: 225, Visits: 449
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???
Post #970344
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse