SQL Server Performance Forum – Threads Archive
nested proc execution
i have one question i created two procedure create procedure test2as
select name from sysobjects
create procedure test1(@select text)
as
set @select =exec test1 but its giving error like
"incorrect syntax near keyword exec" pls help me for this issue
Problem is that you cannot combine SET and EXEC. Also, you need to use parameters with the OUTPUT keyword. Plus your test1 procedure was calling itself, instead of calling test2 – my guess is that this would stop with an error after 32 recurrences. create procedure test2 (@p text output)
as
select @p = name from sysobjects
go create procedure test1(@select text)
as
exec test2 @select output
go For Test2, you’re selecting more or less a random name from sysobjects – but you’re probably just using some air code, right? For Test1, you could also use:
exec @select = test2
perhaps you mean exec proc test1
as
declare @select text
exec @select = test2
go
Thomas,<br /><br />The only way you can have a TEXT type variable is when it is a parameter of a stored procedure. You cannot declare a text type variable in any other way.<br /><br />And I assume you meant CREATE instead of the first EXEC.[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
yeah you’re right and i wouldn’t have bothered if your better post had already appeared!
Better is a relative term.[<img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />]
]]>