From: Sam on
Hi,

I'm trying to execute multiple stored procedures from a parent storedproc.
One of the child stored procedures returns a value which I need for the next
child stored procedure I need to execute in the parent stored procedure. I
used the following syntax but looks like it's not the correct one. Could
someone help me with the correct syntax?

-- Execute child sp and get UserID
DECLARE @UserID uniqueidentifier
SET @UserID = EXEC spNewUserEntry @FirstName = 'John', @LastName = 'Smith'

I appreciate your help with this.
--
Thanks,

Sam
From: Scott Morris on
"Sam" <Sam(a)discussions.microsoft.com> wrote in message
news:40EB7FCF-9705-4254-A0F7-ECB2A2BB9C73(a)microsoft.com...
> Hi,
>
> I'm trying to execute multiple stored procedures from a parent storedproc.
> One of the child stored procedures returns a value which I need for the
> next
> child stored procedure I need to execute in the parent stored procedure. I
> used the following syntax but looks like it's not the correct one. Could
> someone help me with the correct syntax?
>
> -- Execute child sp and get UserID
> DECLARE @UserID uniqueidentifier
> SET @UserID = EXEC spNewUserEntry @FirstName = 'John', @LastName = 'Smith'

Looking up the command in BOL reveals:

[ { EXEC | EXECUTE } ]
{
[ @return_status = ]
{ module_name [ ;number ] | @module_name_var }
[ [ @parameter = ] { value
| @variable [ OUTPUT ]
| [ DEFAULT ]
}
]
[ ,...n ]
[ WITH RECOMPILE ]
}
[;]



From: Sam on
Scott,

Thank you for your response but I've never understood this notation. Could
you please tell me the what you typed to look this up in BOL? Also is there
an example in there?
--
Thanks,

Sam


"Scott Morris" wrote:

> "Sam" <Sam(a)discussions.microsoft.com> wrote in message
> news:40EB7FCF-9705-4254-A0F7-ECB2A2BB9C73(a)microsoft.com...
> > Hi,
> >
> > I'm trying to execute multiple stored procedures from a parent storedproc.
> > One of the child stored procedures returns a value which I need for the
> > next
> > child stored procedure I need to execute in the parent stored procedure. I
> > used the following syntax but looks like it's not the correct one. Could
> > someone help me with the correct syntax?
> >
> > -- Execute child sp and get UserID
> > DECLARE @UserID uniqueidentifier
> > SET @UserID = EXEC spNewUserEntry @FirstName = 'John', @LastName = 'Smith'
>
> Looking up the command in BOL reveals:
>
> [ { EXEC | EXECUTE } ]
> {
> [ @return_status = ]
> { module_name [ ;number ] | @module_name_var }
> [ [ @parameter = ] { value
> | @variable [ OUTPUT ]
> | [ DEFAULT ]
> }
> ]
> [ ,...n ]
> [ WITH RECOMPILE ]
> }
> [;]
>
>
>
> .
>
From: Sam on
Scott,

Got it. Need to use OUTPUT parameters. Thanks again for your help.
--
Thanks,

Sam


"Scott Morris" wrote:

> "Sam" <Sam(a)discussions.microsoft.com> wrote in message
> news:40EB7FCF-9705-4254-A0F7-ECB2A2BB9C73(a)microsoft.com...
> > Hi,
> >
> > I'm trying to execute multiple stored procedures from a parent storedproc.
> > One of the child stored procedures returns a value which I need for the
> > next
> > child stored procedure I need to execute in the parent stored procedure. I
> > used the following syntax but looks like it's not the correct one. Could
> > someone help me with the correct syntax?
> >
> > -- Execute child sp and get UserID
> > DECLARE @UserID uniqueidentifier
> > SET @UserID = EXEC spNewUserEntry @FirstName = 'John', @LastName = 'Smith'
>
> Looking up the command in BOL reveals:
>
> [ { EXEC | EXECUTE } ]
> {
> [ @return_status = ]
> { module_name [ ;number ] | @module_name_var }
> [ [ @parameter = ] { value
> | @variable [ OUTPUT ]
> | [ DEFAULT ]
> }
> ]
> [ ,...n ]
> [ WITH RECOMPILE ]
> }
> [;]
>
>
>
> .
>
From: Scott Morris on
"Sam" <Sam(a)discussions.microsoft.com> wrote in message
news:523A5982-83E2-4CBB-9FB1-0A2F51366D95(a)microsoft.com...
> Scott,
>
> Thank you for your response but I've never understood this notation. Could
> you please tell me the what you typed to look this up in BOL? Also is
> there
> an example in there?

Using the index, simply type in "execute" and then find "execute statement"
in the list.

You tried:

DECLARE @UserID uniqueidentifier
SET @UserID = EXEC spNewUserEntry @FirstName = 'John', @LastName = 'Smith'

Look at the posted documentation and replace @return_status with @UserID,
followed by the rest of your statement. And it is a best practice to fully
qualify your object names with the appropriate schema name. Note that this
is a relatively simply statement - you **really** need to take some time to
learn how to read/understand the documentation. Try "documentation
conventions" in the index as a start.