x

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 )

more ▼

asked Oct 28, 2009 at 01:33 PM in Default

avatar image

Yadyn
36 2 3 4

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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

more ▼

answered Oct 28, 2009 at 02:02 PM

avatar image

Bob Hovious
1.6k 5 9 13

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 28, 2009 at 01:43 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 28, 2009 at 01:50 PM

avatar image

Michael Villegas
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 28, 2009 at 04:01 PM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1069
x461

asked: Oct 28, 2009 at 01:33 PM

Seen: 5853 times

Last Updated: Oct 28, 2009 at 01:33 PM

Copyright 2016 Redgate Software. Privacy Policy