Sql Server

How to Create Temp Tables in Sql Server

Best Example for creation of temp table :

USE [TEST]
GO
/****** Object:  StoredProcedure [Sales].[usp_SalesReport]    Script Date: 05/08/2013 20:00:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*====================================================================================
Name :   [usp_SalesReport]
Purpose : This SP use for Sales Report Page in Admin

=====================================================================================*/
ALTER  PROCEDURE [Sales].[usp_SalesReport]
@mode VARCHAR (20),
@startdate DATE='',
@endDate DATE=''
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TEMP_SalesStatus(
RowID BIGINT IDENTITY(1, 1),
OrderDate VARCHAR(20),
ReportDate VARCHAR(20),
WeekYear VARCHAR(10),
OrderId BIGINT,
CustomerId BIGINT,
ISBN VARCHAR(20),
BookName VARCHAR(1000),
AuthorName VARCHAR(1000),
vBinding VARCHAR(60),
Category VARCHAR(2000),
Price MONEY,
Qty INT,
biPOBookId BIGINT)

CREATE CLUSTERED INDEX IDX_RowID ON #TEMP_SalesStatus(RowID)
CREATE INDEX IDX_OrderDate ON #TEMP_SalesStatus(OrderDate)

DECLARE @NumberRecords BIGINT
DECLARE @RowCNT BIGINT
DECLARE @OrderId BIGINT
DECLARE @vcIsbn VARCHAR(20)
DECLARE @iBookQty INT
DECLARE @smBookPrice MONEY
DECLARE @BookName VARCHAR(1000)
DECLARE @AuthorName VARCHAR(1000)
DECLARE @vBinding VARCHAR(60)
DECLARE @vcBISAC_Code VARCHAR(200)
DECLARE @CategoryCode BIGINT
DECLARE @CategoryNames VARCHAR(2000)
DECLARE @dtOrderDate DATETIME
DECLARE @WeekName VARCHAR(10)
DECLARE @ReportDate DATETIME
DECLARE @biPOBookId BIGINT

IF UPPER(@mode)='SHOW ALL'
BEGIN
INSERT INTO #TEMP_SalesStatus (
OrderDate, OrderId, CustomerId,biPOBookId)

SELECT CONVERT(VARCHAR, dtOrderDate, 103), PO.biOrderId,biUserId,biPOBookId
FROM Sales.PurchaseOrder PO  WITH (NOLOCK) INNER JOIN
Sales.POBooks PB ON PO.biOrderId=PB.biOrderId
        WHERE UPPER(vcPymtStatus)='MADE' ORDER BY dtBankingDate DESC
SET @NumberRecords = @@ROWCOUNT

END
ELSE IF UPPER(@mode) = 'ORDER DATE'
BEGIN
  INSERT INTO #TEMP_SalesStatus (
OrderDate, OrderId, CustomerId, biPOBookId)

SELECT CONVERT(VARCHAR, dtOrderDate, 103), PO.biOrderId,biUserId,biPOBookId
FROM Sales.PurchaseOrder PO  WITH (NOLOCK) INNER JOIN
Sales.POBooks PB ON PO.biOrderId=PB.biOrderId
WHERE UPPER(vcPymtStatus)='MADE' AND CONVERT(DATE,CONVERT(VARCHAR, dtOrderDate, 103),103)
BETWEEN CONVERT(DATE,CONVERT(VARCHAR, @startdate, 103),103) AND  CONVERT(DATE,CONVERT(VARCHAR, @endDate, 103),103)
ORDER BY dtBankingDate DESC

SET @NumberRecords = @@ROWCOUNT
END


SET @RowCNT = 1
WHILE @RowCNT <= @NumberRecords
  BEGIN
SELECT @OrderId = OrderId,@biPOBookId=biPOBookId
FROM #TEMP_SalesStatus
WHERE RowID = @RowCNT

SELECT @vcIsbn=vcIsbn, @iBookQty=iBookQty,
@smBookPrice=smBookPrice
FROM Sales.POBooks WITH (NOLOCK)
WHERE biOrderId= @OrderId AND biPOBookId=@biPOBookId

UPDATE #TEMP_SalesStatus
SET ISBN = @vcIsbn,
Qty = @iBookQty,
Price = @smBookPrice
WHERE OrderId=@OrderId AND biPOBookId=@biPOBookId

SELECT @BookName=vTitle, @AuthorName=vAuthor,
@vBinding=vBinding,@vcBISAC_Code=vcBISAC_Code
FROM Stock.BookDetails WITH (NOLOCK)
WHERE vIsbn = @vcIsbn

SELECT  @CategoryNames=COALESCE(VCCategoryName + ' ', '')
FROM  Stock.BookDetailCategoryMaster WITH (NOLOCK)
WHERE biCategoryId IN (
SELECT  biCategoryId
FROM  Stock.BookDetailCategoryDescription WITH (NOLOCK)
WHERE vcbisac_code IN (SELECT * FROM UF_SplitString(@vcBISAC_Code)))

UPDATE #TEMP_SalesStatus
SET vBinding=@vBinding,
BookName=@BookName,
AuthorName=@AuthorName,
Category = @CategoryNames
WHERE OrderId=@OrderId AND biPOBookId=@biPOBookId

SELECT  @dtOrderDate=dtOrderDate
FROM Sales.PurchaseOrder
WHERE biOrderId= @OrderId

SELECT @WeekName=DATEPART( WEEK , @dtOrderDate)

SELECT @ReportDate= DATEADD(week, @WeekName - 1,
DATEADD(DAY, @@datefirst - DATEPART(weekday, CAST(YEAR(GETDATE()) AS VARCHAR)
+ '-01-01') - 6, CAST(YEAR(GETDATE()) AS VARCHAR) + '-01-01'))

UPDATE #TEMP_SalesStatus
SET WeekYear=@WeekName,
ReportDate=CONVERT(VARCHAR(10),@ReportDate,103)
WHERE OrderId= @OrderId AND biPOBookId=@biPOBookId

SET @RowCNT = @RowCNT + 1

  END

SELECT ISNULL(OrderDate,'N/A') AS OrderDate,
ISNULL(ReportDate,'N/A')AS ReportDate,
ISNULL(WeekYear,'N/A')AS WeekYear,
ISNULL(OrderId ,0)AS OrderId,
ISNULL(CustomerId,0) AS CustomerId,
ISNULL(ISBN,'N/A')AS ISBN,
ISNULL(BookName,'N/A')AS BookName,
ISNULL(AuthorName ,'N/A')AS AuthorName,
ISNULL(vBinding,'N/A')AS vBinding,
ISNULL(Category,'N/A')AS Category,
ISNULL(Price,0)AS Price,
ISNULL(Qty,0)AS Qty
FROM #TEMP_SalesStatus ORDER BY OrderId DESC
DROP TABLE #TEMP_SalesStatus
SET NOCOUNT OFF

END