SQL Subquery return more than 1 row

Question

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

END

David Smith · Answer

I found my mistake. This line is suppose to be the = sign. I have the   instead. Which is totally wrong. PeriodEnd convert ( datetime  @ periodEnd ). However I did this below. But your answer will work also. if ( @ @ROWCOUNT > 0 ) Select @timeSheetBatchId = ( Select MAX ([ TimesheetBatchId ]) From #Temp)

Abdu Rafeeq · Answer

Just use Top in the sql query. Eg: select Top1 * from ExampleTimesheet Where PeriodStart = convert(datetime @periodStart) and [Status] = 3 And PeriodEnd convert(datetime @periodEnd) order by timeSheetBatchId desc;