SQL SERVER – Stored Procedure Optimization Tips – Best Practices

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.

SQL SERVER - Stored Procedure Optimization Tips - Best Practices spoptimizationtricks

  • Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO
  • Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method
  • Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
  • Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')
  • Use the sp_executesql stored procedure instead of the EXECUTE statement.
    The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

  • Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
  • Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
  • Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:
BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

Reference: Pinal Dave (https://blog.sqlauthority.com)

Best Practices, SQL Coding Standards, SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
SQL SERVER – Difference Between Update Lock and Exclusive Lock
Next Post
SQLAuthority News – SQL Server Technical Article – The Data Loading Performance Guide

Related Posts

181 Comments. Leave new

  • CRUD Generator
    November 6, 2011 9:03 am

    Hi Yashwant,

    I think T4S Stored Procedure Generator is good but will it improve performance of my SPs or not?

    Reply
  • Why do u make things complex by adding odd things like “SET NOCOUNT ON;”
    try keep things simple.

    “CEATE PROCEDURE proc_name
    as
    your query”

    thats it

    Reply
    • Nakul Vachhrajani
      November 18, 2011 12:06 am

      Contrary to your comment, SET NOCOUNT ON is quite important. What it ensures is that the number of rows affected is not returned to the caller.

      You may be surprised to know, but it is true that not having SET NOCOUNT ON may result in the application not behaving as it should.

      In my opinion, removing “SET NOCOUNT ON” is not making things simple – it’s making things suicidal!

      Reply
  • Hi Pinal,
    Nice set of tips

    Reply
  • Does adding Comments in stored procedure or function causes ANY KIND of burden on SQL Server performance ?

    Reply
  • Hi Pinal,

    These tips are very nice and helping me allot

    Thanks

    Reply
  • Wonderful article Pinal.
    Thank you!

    Reply
  • Would you suggest using the schema name for all object references in a sproc including joins and conditional clauses?

    For example:
    SELECT b.BatchID, b.BatchName, bt.BatchTransactionID, bt.BatchTransactionName
    FROM dbo.Batch
    INNER JOIN dbo.BatchTrx WITH(NOLOCK) ON dbo.BatchTrx.BatchID = dbo.Batch.BatchID
    WHERE dbo.Batch.StatusID = 1
    AND dbo.BatchTrx.StatusID = 1

    Reply
  • Hello Pinal Dave, thanks you for your blog, is a very good source for me.

    My cuestion is when I execute the select directly in the SP does it reuse the
    execution plan ?, like this

    CREATE PROC dbo.ProcName @p1 int
    AS
    SET NOCOUNT ON;
    –Procedure code here
    SELECT column1 FROM dbo.TblTable1 where ProcID = @p1
    — Reset SET NOCOUNT to OFF
    SET NOCOUNT OFF;
    GO

    Reply
  • Thanks for the excellent Tips..

    Reply
  • Very good tips !

    Reply
  • These Tips are very much useful in optimizing the stored procedures.
    I would like to add one more.
    Minimize the usage of temporary tables,instead use variables to improve the performance

    Reply
  • Dear Pinal,

    I have written a stored procedure but, It takes on an average 1 minute to execute. I tried all tips you suggested, but still that haven’t optimized the query.

    I am a newbie, please guide me to optimize below Stored Procedure

    CREATE PROCEDURE [dbo].[usp_GetMessagesCountBySearchFilter]
    (
    @SearchText NVARCHAR(1000),
    @FromDate DATETIME,
    @ToDate DATETIME,
    @IsPending BIT,
    @Sender NVARCHAR(1000),
    @Receiver NVARCHAR(1000)
    )
    AS
    BEGIN
    –SET NOCOUNT ON;

    DECLARE
    @local_SearchText NVARCHAR(1000),
    @local_FromDate DATETIME,
    @local_ToDate DATETIME,
    @local_IsPending BIT,
    @local_Sender NVARCHAR(1000),
    @local_Receiver NVARCHAR(1000)

    SET @SearchText= @local_SearchText
    SET @FromDate =@local_FromDate
    SET @ToDate =@local_ToDate
    SET @IsPending=@local_IsPending
    SET @Sender =@local_Sender
    SET @Receiver =@local_Receiver

    DECLARE @MessageCount INT
    SELECT @MessageCount=COUNT(MessageID) FROM
    (
    SELECT
    DISTINCT T1.MessageID,
    MessageSubject,
    MessageBody,
    PostedDateTime,
    (
    SELECT
    CAST(T4.UserID AS NVARCHAR(120))
    +’,’
    FOR XML PATH(”)
    ) AS PostedForIds,
    (
    SELECT
    CASE T4.IsOrganization
    WHEN 1 THEN ISNULL(T4.CompanyName,ISNULL(T4.FirstName+’ ‘+T4.LastName,T4.UserName))
    ELSE ISNULL(T4.FirstName+’ ‘+T4.LastName,T4.UserName)
    END
    +’,’
    FOR XML PATH(”)
    ) AS PostedForNames,
    T2.FromUserId AS PostedById,
    CASE T3.IsOrganization
    WHEN 1 THEN ISNULL(T3.CompanyName,ISNULL(T3.FirstName+’ ‘+T3.LastName,T3.UserName))
    ELSE ISNULL(T3.FirstName+’ ‘+T3.LastName,T3.UserName)
    END AS PostedByName,
    (SELECT COUNT(UserId) FROM dbo.tblMessageInbox T4 WHERE T4.MessageId=T2.MessageId) AS SentToCount,
    (
    SELECT CASE
    WHEN COUNT(UserId)>0 THEN 1
    ELSE 0
    END
    FROM dbo.tblMessageInbox T5
    WHERE T5.MessageId=T2.MessageId
    AND
    T5.IsPending=1
    ) AS IsPending
    FROM dbo.tblMessageDetails T1 LEFT OUTER JOIN dbo.tblMessageInbox T2
    ON T1.MessageID=T2.MessageID
    LEFT OUTER JOIN uvw_UserCompleteInformation T3
    ON T2.FromUserID=T3.UserID
    LEFT OUTER JOIN uvw_UserCompleteInformation T4
    ON T2.UserID=T4.UserID
    WHERE MessageType=’M’

    UNION ALL

    SELECT
    DISTINCT T2.MessageID,
    T1.MessageSubject,
    T1.MessageBody,
    T1.PostedDateTime,
    T2.ToUserIDs AS PostedForIds,
    T2.ToUserNames AS PostedForNames,
    T2.UserID AS PostedById,
    CASE T3.IsOrganization
    WHEN 1 THEN ISNULL(T3.CompanyName,ISNULL(T3.FirstName + ‘ ‘ + T3.LastName,T3.UserName))
    ELSE ISNULL(T3.FirstName + ‘ ‘ + T3.LastName,T3.UserName)
    END AS PostedByName,
    (SELECT COUNT(*) FROM dbo.fnSplit(T2.ToUserIDs,’,’)) AS SentToCount,
    0 AS IsPending
    FROM dbo.tblMessageDetails T1 LEFT OUTER JOIN dbo.tblMessageSentbox T2
    ON T1.MessageID = T2.MessageID
    LEFT OUTER JOIN uvw_UserCompleteInformation T3
    ON T2.UserID = T3.UserID
    WHERE
    T2.MessageID NOT IN
    (
    SELECT DISTINCT(MessageID) FROM dbo.tblMessageInbox
    )
    AND T1.MessageType=’M’
    ) AS T
    WHERE
    T.PostedForIds IS NOT NULL
    AND
    T.PostedDateTime BETWEEN @local_FromDate AND @local_ToDate
    AND
    (
    T.MessageSubject LIKE ‘%’+
    CASE
    WHEN @local_SearchText =”
    THEN T.MessageSubject
    ELSE @local_SearchText
    END + ‘%’
    OR
    T.MessageBody LIKE ‘%’+
    CASE
    WHEN @local_SearchText =”
    THEN T.MessageBody
    ELSE @local_SearchText
    END + ‘%’
    )
    AND
    T.PostedByName LIKE ‘%’+
    CASE
    WHEN @local_Sender =”
    THEN T.PostedByName
    ELSE @local_Sender
    END + ‘%’
    AND
    T.PostedForNames LIKE ‘%’+
    CASE
    WHEN @local_Receiver =”
    THEN T.PostedForNames
    ELSE @local_Receiver
    END + ‘%’
    AND
    IsPending=@local_IsPending

    SELECT @MessageCount
    END


    Thank You,
    Amol

    Reply
  • prince sharma
    March 19, 2012 7:49 pm

    very usefull information. it really help me .

    Reply
  • nice article….suggest me how to become expertise on sql server stored procedures

    Reply
  • Hi Pinal,

    Could you please tell me the “script ” for long running Queries/Stored procedures from last 24 hrs .It will help us to optimize Stored procedures.

    Reply
  • Nice Article….

    Reply
  • Nice topic
    I face same problem, Please check my Procedure

    ALTER PROCEDURE [dbo].[SP_ExtendedPTU]
    (
    @SQLForPTU as varchar(MAX)
    )
    AS
    BEGIN TRAN
    –DECLARE
    –@SQLForPTU as varchar(MAX)
    –SET @SQLForPTU= ‘WHERE OrderID IN(4233)And OrderType =3’

    BEGIN–1
    DECLARE
    @ProductionTracingUnitID as int,
    @OrderID as int,
    @OrderType as smallint,
    @ProductID as int,
    @LabLabDipID as int,
    @ColorName as varchar(255),
    @PantonNo as varchar(127),
    @Shade as varchar(4),
    @DyeingOrderQty as decimal(30, 17),
    @JobOrderQty as decimal(30, 17),
    @ProductionPipeLineQty as decimal(30, 17),
    @ProductionFinishedQty as decimal(30, 17),
    @DeliveryQty as decimal(30, 17),
    @BuyerID as int,
    @FactoryID as int,
    @ProductionGraceQty as decimal(30, 17),
    @WeightLossGainQty as decimal(30, 17),
    @RateInLBS as decimal(30, 17),
    @State as smallint,
    @ProductionLossQty as decimal(30, 17),
    @ReturnQty as decimal(30, 17),
    @ActualDeliveryQty as decimal(30,17),
    @ReadyStockInhand as decimal(30, 17),
    @JobOrderQtyApproved as decimal(30, 17),
    @OrderNumber as varchar(50),
    @FactoryName as varchar(200),
    @BuyerName as varchar(200),
    @ProductName as varchar(200),
    @YetToDelivery as decimal(30,17),
    @StockInHand as decimal(30,17),
    @YetToProduction as decimal(30,17),
    @LCID as int,
    @LCNo as varchar(300),
    @PIQty as decimal(30,17),
    @ChangingQty as decimal(30,17),
    @SampleAdjQty as decimal(30,17),
    @SampleAdjValue as decimal(30,17),
    @MKTPersonID as int,
    @MKTPersonName as varchar(500),
    @MerchandiserID as int,
    @MerchandiserName as varchar(500),
    @AmendmentStatus as smallint,
    @AcceptanceValue as decimal(30,17),
    @MaturityValue as decimal(30,17),
    @BillAcceptanceValue as decimal(30,17),
    @BillMaturityValue as decimal(30,17),
    @BillAcceptancePercentage as decimal(30,17),
    @BillMaturityPercentage as decimal(30,17),
    @ExportLCValue as decimal(30,17),
    @Acceptance as varchar(100),
    @Maturity as varchar(100),
    @YarnCount as varchar(50),
    @PTUDQty as decimal(30,17),
    @ShadeFromOrder as smallint,
    @EWYDLRelabNo as varchar(100),
    @EWYDLColorNo as varchar(100),
    @DeliveryTo as int,
    @FactoryPersonnelID as int,
    @BuyerPersonnelID as int,
    @OrderRcvBy as int,
    @OrderState as smallint

    CREATE TABLE #TempTableOne(
    ProductionTracingUnitID int,
    OrderID int,
    OrderType smallint,
    ProductID int,
    DyeingOrderQty decimal(30,17),
    JobOrderQty decimal(30,17),
    ProductionPipeLineQty decimal(30,17),
    ProductionFinishedQty decimal(30,17),
    DeliveryQty decimal(30,17),
    BuyerID int,
    FactoryID int,
    ProductionGraceQty decimal(30,17),
    WeightLossGainQty decimal(30,17),
    RateInLBS decimal(30,17),
    ProductionLossQty decimal(30,17),
    ActualDeliveryQty decimal(30,17),
    ReadyStockInhand decimal(30,17),
    OrderNumber varchar(50),
    FactoryName varchar(200),
    BuyerName varchar(200),
    ProductName varchar(200),
    ColorName varchar(200),
    LabLabDipID int,
    ReturnQty decimal(30,17),
    YetToDelivery decimal(30,17),
    StockInHand decimal(30,17),
    YetToProduction decimal(30,17),
    LCID int,
    LCNo varchar(300),
    PIQty decimal(30,17),
    ChangingQty decimal(30,17),
    SampleAdjQty decimal(30,17),
    SampleAdjValue decimal(30,17),
    MKTPersonID int,
    MKTPersonName varchar(500),
    MerchandiserID int,
    MerchandiserName varchar(500),
    AmendmentStatus smallint,
    Acceptance varchar(100),
    Maturity varchar(100),
    YarnCount varchar(50),
    EWYDLRelabNo varchar(50),
    EWYDLColorNo varchar(50),
    ShadeFromOrder smallint
    )

    –ProductionTracingUnitID,OrderID,OrderType,ProductID,LabLabDipID,ColorName,PantonNo,EWYDLColorNo,Shade,EWYDLRelabNo,DyeingOrderQty,JobOrderQty,ProductionPipeLineQty,ProductionFinishedQty,DeliveryQty,BuyerID,FactoryID,ProductionGraceQty,WeightLossGainQty,RateInLBS,State,ProductionLossQty,ShadeFromOrder,ReturnQty,ActualDeliveryQty,ReadyStockInhand,JobOrderQtyApproved
    DECLARE
    @SQL as varchar(MAX)
    SET @SQL=

    DECLARE Cur_AB1 CURSOR GLOBAL FORWARD_ONLY KEYSET FOR
    SELECT ProductionTracingUnitID,OrderID,OrderType,ProductID,DyeingOrderQty,JobOrderQty,ProductionPipeLineQty,ProductionFinishedQty,BuyerID,FactoryID,ProductionGraceQty,WeightLossGainQty,RateInLBS,ProductionLossQty,ActualDeliveryQty,ReadyStockInhand,ColorName,LabLabDipID,ReturnQty,EWYDLRelabNo,EWYDLColorNo,ShadeFromOrder FROM ProductionTracingUnit ‘+@SQLForPTU+’

    EXEC (@SQL)

    OPEN Cur_AB1
    FETCH NEXT FROM Cur_AB1 INTO @ProductionTracingUnitID,@OrderID,@OrderType,@ProductID,@DyeingOrderQty,@JobOrderQty,@ProductionPipeLineQty,@ProductionFinishedQty,@BuyerID,@FactoryID,@ProductionGraceQty,@WeightLossGainQty,@RateInLBS,@ProductionLossQty,@ActualDeliveryQty,@ReadyStockInhand,@ColorName,@LabLabDipID,@ReturnQty,@EWYDLRelabNo,@EWYDLColorNo,@ShadeFromOrder
    WHILE(@@Fetch_Status -1)
    BEGIN–2
    SET @LCID=0
    SET @LCNo=”
    SET @PIQty=0
    SET @AcceptanceValue =0
    SET @MaturityValue= 0
    SET @Acceptance= ”
    SET @Maturity =”

    SET @DeliveryQty=@ActualDeliveryQty-@ReturnQty
    SET @YetToDelivery=@JobOrderQty-@ActualDeliveryQty+@ReturnQty
    set @PTUDQty=(select sum(Qty) from PTUDistribution where ProductionTracingUnitID=@ProductionTracingUnitID )
    IF(@PTUDQty>@YetToDelivery)
    BEGIN–sih
    SET @StockInHand =@YetToDelivery
    END –sih
    ELSE
    BEGIN–sih2
    SET @StockInHand =@PTUDQty
    END –sih2
    SET @YetToProduction=@JobOrderQty-@ReadyStockInhand-@ActualDeliveryQty+@ReturnQty
    IF (@YetToProduction0 and @ExportLCValue is not null)
    BEGIN
    SET @BillAcceptancePercentage =(@BillAcceptanceValue*100)/@ExportLCValue — bill Percentage
    SET @BillMaturityPercentage =(@BillMaturityValue*100)/@ExportLCValue

    SET @AcceptanceValue=(@ChangingQty*@RateInLBS)*(@BillAcceptancePercentage/100)–Percentage Wise PI Valu
    SET @MaturityValue=(@ChangingQty*@RateInLBS)*(@BillMaturityPercentage/100)

    IF((@ChangingQty*@RateInLBS)>0 and (@ChangingQty*@RateInLBS) is not null)
    BEGIN
    SET @AcceptanceValue=(@AcceptanceValue*100)/(@ChangingQty*@RateInLBS)– PI ValuePercentage
    SET @MaturityValue=(@MaturityValue*100)/(@ChangingQty*@RateInLBS)
    END

    SET @Acceptance=Convert(varchar(20),(CONVERT(float,round((@AcceptanceValue+@MaturityValue),0)))) +’%’
    SET @Maturity =Convert(varchar(20),(CONVERT(float,round(@MaturityValue,0)))) +’%’
    END

    SET @FactoryName=”
    IF(@FactoryID>0)
    BEGIN–jam3
    SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@FactoryID)
    END–jam3

    SET @BuyerName=”
    IF(@BuyerID>0)
    BEGIN–jam4
    SET @BuyerName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
    END–jam4

    END–jam1
    ELSE
    BEGIN –jam2 IF Sample
    SET @LCID=0
    SET @LCNo=”
    SET @OrderNumber=”
    SET @PIQty=0
    SET @DeliveryTo=0
    SET @FactoryPersonnelID=0
    SET @BuyerPersonnelID=0
    SET @OrderRcvBy=0
    SET @ChangingQty=0
    SET @MerchandiserName=”
    SET @MKTPersonName=”
    SET @OrderState=0
    SET @AmendmentStatus=0
    SET @PIQty= (SELECT ISNULL(SUM(Qty),0) FROM SampleOrderDetail WHERE PTUID=@ProductionTracingUnitID)
    SET @ChangingQty=@PIQty
    SELECT @OrderNumber=ISNULL(SampleOrderNo,”), @DeliveryTo=ISNULL(DeliveryTo,0),@FactoryPersonnelID=ISNULL(FactoryPersonnelID,0),@BuyerPersonnelID=ISNULL(BuyerPersonnelID,0),@OrderRcvBy=ISNULL(OrderRcvBy,0),@OrderState=ISNULL(OrderState,0) FROM SampleOrder WHERE SampleOrderID=@OrderID
    SET @AmendmentStatus=@OrderState
    IF(@DeliveryTo=3)
    BEGIN
    SET @MerchandiserName=(SELECT ISNULL([Name],”) FROM ContactPersonnel WHERE ContactPersonnelID=@FactoryPersonnelID)
    END

    IF(@DeliveryTo=2)
    BEGIN
    SET @MerchandiserName=(SELECT ISNULL([Name],”) FROM ContactPersonnel WHERE ContactPersonnelID=@BuyerPersonnelID)
    END
    SET @MKTPersonName =(SELECT [Name] FROM Employee WHERE EmployeeID=@OrderRcvBy)

    IF(@DeliveryTo=3)
    BEGIN
    SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@FactoryID)
    END

    IF(@DeliveryTo=2)
    BEGIN
    SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
    END

    SET @BuyerName=”
    IF(@BuyerID>0)
    BEGIN–jam4
    SET @BuyerName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
    END–jam4
    END–jam2

    SET @ProductName=”
    SET @YarnCount =”
    SELECT @ProductName=(‘[‘+ Code+ ‘] ‘+ [Name]), @YarnCount =[Count] FROM Yarncategory WHERE YarncategoryID=@ProductID

    INSERT INTO #TempTableOne Values(ISNULL(@ProductionTracingUnitID,0),ISNULL(@OrderID,0),ISNULL(@OrderType,0),ISNULL(@ProductID,0),ISNULL(@DyeingOrderQty,0),ISNULL(@JobOrderQty,0),ISNULL(@ProductionPipeLineQty,0),ISNULL(@ProductionFinishedQty,0),ISNULL(@DeliveryQty,0),ISNULL(@BuyerID,0),ISNULL(@FactoryID,0),ISNULL(@ProductionGraceQty,0),ISNULL(@WeightLossGainQty,0),ISNULL(CONVERT (decimal(18,2),@RateInLBS),0),ISNULL(@ProductionLossQty,0),ISNULL(@ActualDeliveryQty,0),ISNULL(@ReadyStockInhand,0),ISNULL(@OrderNumber,”),ISNULL(@FactoryName,”),ISNULL(@BuyerName,”),ISNULL(@ProductName,”),ISNULL(@ColorName,”),ISNULL(@LabLabDipID,0),ISNULL(@ReturnQty,0),ISNULL(@YetToDelivery,0),ISNULL(@StockInHand,0),ISNULL(@YetToProduction,0),ISNULL(@LCID,0),ISNULL(@LCNo,”),ISNULL(@PIQty,0),ISNULL(@ChangingQty,0),ISNULL(@SampleAdjQty,0),ISNULL(@SampleAdjValue,0),ISNULL(@MKTPersonID,0),ISNULL(@MKTPersonName,”),ISNULL(@MerchandiserID,0),ISNULL(@MerchandiserName,”),ISNULL(@AmendmentStatus,0),ISNULL(@Acceptance,”),ISNULL(@Maturity,”),ISNULL(@YarnCount,”),ISNULL(@EWYDLRelabNo,”),ISNULL(@EWYDLColorNo,”),ISNULL(@ShadeFromOrder,0))
    FETCH NEXT FROM Cur_AB1 INTO @ProductionTracingUnitID,@OrderID,@OrderType,@ProductID,@DyeingOrderQty,@JobOrderQty,@ProductionPipeLineQty,@ProductionFinishedQty,@BuyerID,@FactoryID,@ProductionGraceQty,@WeightLossGainQty,@RateInLBS,@ProductionLossQty,@ActualDeliveryQty,@ReadyStockInhand,@ColorName,@LabLabDipID,@ReturnQty,@EWYDLRelabNo,@EWYDLColorNo,@ShadeFromOrder
    END–2
    CLOSE Cur_AB1
    DEALLOCATE Cur_AB1
    SELECT * FROM #TempTableOne Order By OrderID
    –Group By Product
    SELECT ProductID,ProductName, YarnCount, SUM(PIQty) as PIQty, SUM(ChangingQty) AS ChangingQty, SUM(SampleAdjQty) AS SampleAdjQty, SUM(SampleAdjValue) as SampleAdjValue, SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY ProductID,ProductName,YarnCount Order By ProductID
    –Group By Factory
    SELECT FactoryID,FactoryName,SUM(PIQty) as PIQty, SUM(ChangingQty) AS ChangingQty, SUM(SampleAdjQty) AS SampleAdjQty, SUM(SampleAdjValue) as SampleAdjValue, SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY FactoryID,FactoryName
    –Group By Order
    SELECT OrderID,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,SUM(PIQty) AS PIQty,SUM(ChangingQty) AS ChangingQty,SUM(SampleAdjQty) AS SampleAdjQty,SUM(SampleAdjValue) AS SampleAdjValue,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity,SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY OrderID,OrderNumber,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity
    –Default View
    SELECT OrderID,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,ProductID,ProductName,YarnCount,PIQty,ChangingQty,SampleAdjQty,SampleAdjValue,RateInLBS,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity, SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY OrderID,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,ProductID,ProductName,YarnCount,PIQty,ChangingQty,SampleAdjQty,SampleAdjValue,RateInLBS,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity
    DROP TABLE #TempTableOne
    END–1
    COMMIT TRAN

    Note : At a time i want to get more than 500000 record from this store procedure .

    Reply
  • Hi Pinal,
    I am required to write stored procedure in sql server to get:
    -the number of sessions on daily and hourly basis
    -number of unique users
    -Number of first time users.
    i have a table with 100’s of rows & columns :
    RequestID,ApplicationID,SessionID,UserId,UserName,RequestURL,RequestTimeStamp,InsertTimestampGMT,UpdateTimestampGMT,Idx
    Please help!

    Reply
  • Aamir Ali Ansari
    May 27, 2012 7:00 pm

    Nice and simple article. Really helpful for those who constantly look for improvement.

    Reply
  • Hi,

    is there any difference between
    select top 1 * from table where condition

    and

    select 1 from table where condition

    Reply
    • “select Top 1 * ” returns 1 row which satisfies the given condition.
      where as “Select 1” returns Number 1 if there is a record in that table which satisfies the given condition

      Reply

Leave a Reply