I get an error that subquery returns more than one value. When there are more than 2 rows, I want to grab the max
TimesheetBatchId. Can someone help me modify the procedure to choose the row with greater or max TimesheetBatchID.
Example: TimesheetBatch Table Below
TimesheetBatchId PeriodStart PeriodEnd Status
2 12/5/2015 12/11/2015 3
4 12/5/2015 12/11/2015 3
8 12/5/2015 12/11/2015 3 -- I should get this row because 8 is greater and the max
ALTER PROCEDURE [dbo].[GetBatchIdByPeriodStartAndStatus]
@periodStart datetime,
@periodEnd datetime,
@timeSheetBatchId int output
AS BEGIN
SELECT Distinct [TimesheetBatchId]
,[RptHdrId]
,[DateCreated]
,[PeriodStart]
,[PeriodEnd]
,[BatchID] ,
[Status]
INTO #Temp
FROM [ExampleTimesheet].[dbo].[TimeSheetBatch]
Where PeriodStart = convert(datetime, @periodStart)
and [Status] = 3 And PeriodEnd <> convert(datetime, @periodEnd)
BEGIN
if( @@ROWCOUNT > 0)
Select @timeSheetBatchId = (Select [TimesheetBatchId] From #Temp)
END
Drop Table #Temp