Five things to remember to improve the stored procedure performance while create stored procedure in SQL Server

Steps to improve the performance  of Stored procedure in SQL Server

 1) Set NOCOUNT ON;
 2) Don't use input parameters directly. Assign them to Locally  declared variables in stored procedure.
 3) Don't use Count(*) use Count(pk_Id)
 4) Use Exists instead of IN in where condition
 5) Use distinct instead of Group by clause in all possible cases

Example stored procedure
 Here my using group by clause to full file my requirement. Try to avoid the using of group by clause

ALTER PROCEDURE [dbo].[your sp name here] 

@warehouseId char(4)

AS

BEGIN

    SET NOCOUNT ON;

    Declare @Local_warehouseId char(4)=@warehouseId

   

    select oh.OrderNum,case when od.picked is null then 0 else od.picked end as picked,oh.numLines,mem.firstName,mem.lastName,mem.phone,mem.Suburb,oh.memberid as memberID,oh.PickUpPointTimeSlot as EstPickUpTime  from OrderHeader oh join 

members mem on mem.memberID=oh.memberid

join PickupPointTimes pPoint on pPoint.PickupPointID=oh.PickupPointID and oh.PickUpPointTimeSlot=pPoint.TimeSlot and pPoint.PickUpDay=oh.pickupDay

Left outer join (select OrderNum,COUNT(LineItemID) as picked from OrderDetails where PickedQTY is not null group by OrderNum )  as od  on od.OrderNum=oh.OrderNum 

where exists (select * from PickupPoints pp where pp.PickupPointID=oh.PickupPointID and WarehouseID=@Local_warehouseId) and oh.status='RP'

order by pPoint.SortCode,oh.numLines

 

END