How to Get Top Value from a Stored Procedure-CodePudding

Is there a way to get top row from a stored procedure ?

SQL Server stored procedure

EXEC SelectedJobTypeList
┌─────────────┬──────────────┐
│ JobTypeCode | JobTypeName  |
├─────────────┼──────────────┤
│             | Select All   |
├─────────────┼──────────────┤
│ A1          | Road work    |
├─────────────┼──────────────┤
│ B1          | Design       |
├─────────────┼──────────────┤
│ C1          | Build        |
└─────────────┴──────────────┘

Expected Result

┌─────────────┬──────────────┐
│ JobTypeCode | JobTypeName  |
├─────────────┼──────────────┤
│             | Select All   |
└─────────────┴──────────────┘

My Solution

SELECT '' as JobTypeCode, 'Select All' as JobTypeName

CodePudding user response：

Your example may be oversimplified because it doesn't appear necessary to call a SP just to be returned "Select All". However assuming you do need to call the SP in your case, you would have far more control if you capture the results into a temp table and then select the row you are interested in. And while you could use TOP 1 you will have a more robust solution if you have a way to select a specific row.

Here is an example

-- Ensure these datatypes exactly match the data being returned by your SP
CREATE TABLE #tempJobTypeList (JobTypeCode VARCHAR(2), JobTypeName VARCHAR(32));

INSERT INTO #tempJobTypeList (JobTypeCode, JobTypeName)
EXEC SelectedJobTypeList;

SELECT JobTypeCode, JobTypeName
FROM #tempJobTypeList
WHERE JobTypeName = '';

DROP TABLE #tempJobTypeList;