CODEWITHSUNDEEP
sql-serverstored-procedures
datps
datps

Reputation: 768

How to Return Fewer Columns from a Stored Procedure?

I have a legacy stored procedure that accepts a list of ids via a table type that is currently being used directly throughout the system:

CREATE PROCEDURE [dbo].[DataLoadByList]
(
    @CaseIds [dbo].[TTIdsList] READONLY
)
AS
BEGIN
    SELECT *
    FROM tblCases cases
    INNER JOIN @CaseIds Ids ON cases.CaseId = Ids.Id
END
GO

I also have a similar legacy stored procedure that performs the same query on a single id (input param) only but returns two less columns (i.e. not using * in the select but rather distinct column names).

The two share over 90% of the code, resulting in code duplication and requiring double-writing of every change...

I would like to re-implement the single id stored procedure such that it only calls the table-based stored procedure, but before returning the query result, filter it out such that it returns exactly what it used to return ("same number of of columns minus two").

Is there a way in SQL Server to accomplish that?

Upvotes: 1

Views: 148

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

In the single-id procedure, EXECUTE the table-based procedure, storing the results in a temporary table (or table variable).

Then SELECT only the desired columns from the temporary table.

Upvotes: 3

Related Questions