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

Yadyn gravatar image

Yadyn
36 1 1 1

(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

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

Formatting problem was because it was in the middle of an ordered list. Putting pre tags round it gives you something close to what you want (no highlighting, but then it's pretty rubbish with that anyway!)
Oct 28, 2009 at 02:11 PM Melvyn Harbour 1 ♦♦
Melvin, I figured it out and you just trashed my edits !! :) :) :)
Oct 28, 2009 at 02:12 PM Bob Hovious
Have them back in now, and thanks for the tip about the pre tags.
Oct 28, 2009 at 02:17 PM Bob Hovious
(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

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(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, @Status, @Name, @Address
more ▼

answered Oct 28, 2009 at 01:50 PM

Michael Villegas gravatar image

Michael Villegas
11 1 1 1

Stored procedures certainly do return values - see http://msdn.microsoft.com/en-us/library/ms174998.aspx
Oct 28, 2009 at 02:01 PM Matt Whitfield ♦♦
(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x986
x413

asked: Oct 28, 2009 at 01:33 PM

Seen: 3883 times

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