CODEWITHSUNDEEP
sqlstored-procedures
mattgcon
mattgcon

Reputation: 4848

Insert SQL stored procedure values into another stored procedure

I have a large stored procedure that returns a record for a person, there are four fields that I need to return very specific values for. I have another stored procedure that performs this specific action.

The small stored procedure is as follows:

SELECT   TOP 1
    wea.field,
    wea.degree,
    wea.degreeyear,
    wpp.ProgramCategory
FROM dbo.webeventaffiliation wea
LEFT JOIN dbo.WebProgramParticipants wpp
    ON 
        wea.userid = wpp.UserID AND
        wea.eventid = wpp.eventid
INNER JOIN dbo.WebProgramCategoryDescriptions wpcd
    ON 
        wpcd.ProgramCategory = wpp.ProgramCategory
WHERE wea.UserID = @UserID
    ORDER BY wea.datelastmodified DESC

LARGE STORED PROCEDURE SAMPLE RETURN DATA:

Name: XXXXX
Address: XXXXX
Field: [small stored procedure value]
Degree: [small stored procedure value] 
DegreeYear: [small stored procedure value]
ProgramCategory: [small stored procedure value]

My question is how do I get the 4 data items from this stored procedure into their respective columns within the dataset that is returned from the large stored procedure?

Upvotes: 0

Views: 642

Answers (3)

MatBailie
MatBailie

Reputation: 86706

As your small stored procedure doesn't write anything, you could just write it as a table valued function.

You can then apply the function to an entire data-set by using APPLY.

(Table valued functions that are written INLINE (not multi-statement) are then explanded macro-like to execute extremely efficiently. This is perfect for your description as the function would just be a single SELECT statement already.)


The Function:

CREATE FUNCTION dbo.your_function(@user_id AS INT)
RETURNS TABLE
AS
RETURN
  <your query>


The function used in a query inside your big SP:

SELECT
  ...,
  user_detail.field,
  user_detail.degree,
  user_detail.degreeyear,
  user_detail.programcategory
FROM
  ...
CROSS APPLY
  dbo.your_function(some_table.user_id) AS user_detail


In general I use functions to encapsulate queries, and only wrap them up in Stored Procedures if...
1) I need to write data. (Functions can't INSERT, UPDATE or DELETE)
2) I want to create an API like interface to client applications.

Upvotes: 1

Glenn
Glenn

Reputation: 156

Using a table-valued function instead of a stored procedure could be helpful. You will be able to use the TVF just like a table ie:

SELECT
  COLUMNS_NAMES
FROM
  TVF(PARAMS)

Upvotes: 2

Yuck
Yuck

Reputation: 50835

Since you're getting only one row with four values you could use OUTPUT parameters:

EXECUTE SomeSmallerProcedure
    @field OUTPUT, @degree OUTPUT, @degreeyear OUTPUT, @ProgramCategory OUTPUT;

Your procedure listed above would change to:

ALTER PROCEDURE SomeSmallerProcedure
  @field varchar(255) OUTPUT,
  @degree varchar(255) OUTPUT,
  @degreeyear varchar(255) OUTPUT,
  @ProgramCategory varchar(255) OUTPUT
AS BEGIN SET NOCOUNT ON;
  SELECT TOP 1
    @field = wea.field,
    @degree = wea.degree,
    @degreeyear = wea.degreeyear,
    @ProgramCategory = wpp.ProgramCategory
  -- ... rest as before

The signature of your procedure above would have to include those parameters explicitly for OUTPUT.

Upvotes: 0

Related Questions