Can I call a stored procedure as part of a statement?

Question

question

Yadyn asked Oct 28, '09

Can I call a stored procedure as part of a statement?

Is it possible to do something like the following inside of a stored procedure?

INSERT
INTO    [Database].[Schema].[Employees]
        ( [EmployeeID],
          [WindowsLogonID],
          [Status],
          [Name],
          [Address] )
VALUES  ( @EmployeeID,
          (EXEC [usp_GetWindowsLogonIDByEmpID] @EmpID = @EmployeeID),
          @Status,
          @Name,
          @Address )

Where, say, some other table keeps more info on employees and their company Windows computer-use stuff. After all, not all employees may have or need computer access. The alternative is to pass it in as a parameter much like the other four columns... but if the database can look it up itself...?

Essentially, then, this other stored procedure would return only the WindowsLogonID, taking an EmployeeID as a parameter to look it up, and thus automatically provide the value for this INSERT statement.

Would I be better off (or stuck) doing it like this?

DECLARE @WindowsLogonID int

EXEC    @WindowsLogonID = [usp_GetWindowsLogonIDByEmpID]
        @EmpID = @EmployeeID

INSERT
INTO    [Database].[Schema].[Employees]
        ( [EmployeeID],
          [WindowsLogonID],
          [Status],
          [Name],
          [Address] )
VALUES  ( @EmployeeID,
          @WindowsLogonID,
          @Status,
          @Name,
          @Address )

t-sql stored-procedures

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-28T14:02:08Z

Bob Hovious answered Oct 28, '09

Sorry but you can't. However, you do have some options.

If only one value is being returned, you can rewrite your stored procedure as a user-defined scalar function, and call it like this.

    INSERT INTO [Database].[Schema].[Employees]
            ( [EmployeeID],
              [WindowsLogonID],
              [Status],
              [Name],
              [Address] )
    SELECT  @EmployeeID,
            dbo.tfn_GetWindowsLogonIDByEmpID (@EmployeeID),
            @Status,
            @Name,
            @Address

If multiple values are being returned by your stored proc, you can rewrite it as a table-valued function and call it like this.

    INSERT INTO    [Database].[Schema].[Employees]
            ( [EmployeeID],
              [WindowsLogonID],  
              [SomeOtherColumn],
              [Status],
              [Name],
              [Address] )
    SELECT  @EmployeeID,
              tfn.[WindowsLogonID],  -- from the table valued function
              tfn.[SomeOtherColumn], -- also from the function
              @Status,
              @Name,
              @Address
    FROM dbo.tfn_GetWindowsLogonIDByEmpID (@EmployeeID) tfn

However, you may wish to preserve your code as a stored proc, because the procedure does some other things which functions can't normally do, such as logging or other data manipulation, or calling other procedures. (I say "normally" because CLR makes a number of things possible, but that is far outside the scope of your question.)

If this is the case, just catch the value(s) from your stored procedure into a temporary table or table variable and do your final insert from there.

    CREATE TABLE #temp (with schema to match output of stored procedure)

    INSERT INTO #temp
    EXEC [usp_GetWindowsLogonIDByEmpID] @EmpID = @EmployeeID)

    INSERT INTO    [Database].[Schema].[Employees]
            ( [EmployeeID],
              [WindowsLogonID],  
              [SomeOtherColumn],
              [Status],
              [Name],
              [Address] )
    SELECT  @EmployeeID,
              tfn.[WindowsLogonID],  -- from #temp
              tfn.[SomeOtherColumn], -- also from #temp
              @Status,
              @Name,
              @Address
    FROM #temp tfn

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-28T13:43:00Z

Matt Whitfield answered Oct 28, '09

You need to do the latter really. The execution of a stored procedure is a statement, and not an expression, and insert values lists only take expressions.

Take a look at this question.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-10-28T13:50:48Z

Michael Villegas answered Oct 28, '09

You can't call a stored procedure inside an insert statement, but you can call a user defined function, in fact that is what you should do because a scalar function can return only one value mean while a stored procedure does not return any value.

The code will be something like this

INSERT INTO    [Database].[Schema].[Employees]
        ( [EmployeeID],
          [WindowsLogonID],
          [Status],
          [Name],
          [Address] )  
SELECT   @EmployeeID,
         [dbo].[ufn_GetWindowsLogonIDByEmpID](@EmpID),
          @Status,
          @Name,
          @Address

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2009-10-28T16:01:40Z

Grant Fritchey answered Oct 28, '09

One other option is to take whatever the procedure is doing and turn it into a common table expression (CTE) within your code. That way you can refer to it almost like it was a table.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question