Re: Temp table.


CREATE PROCEDURE [dbo].[SPSelectOrdersByCategory]
(
@EntityID VARCHAR(10),
@EntityspecificID INT,
@StartDate DATETIME,
@EndDate DATETIME
)

AS

DECLARE @SQLStatement VARCHAR(8000),
@Col VARCHAR(8000)

SET @Col = (SELECT DBO.FNGetAssignCategory(@EntityID,@EntityspecificID))
PRINT @Col

IF @Col <> ''
BEGIN
SET @SQLStatement = 'CREATE TABLE dbo.OrderDate (Dates VARCHAR(20),
EntityID VARCHAR(10),
--- IsNonOrder BIT,
EntityspecificID INT, IsLock BIT,'+@Col+',TotalAmount AS
(dbo.FNGetTotalAmountbyEntity(EntityID,EntityspecificID,Dates)) )'
END
ELSE
BEGIN
SET @SQLStatement = 'CREATE TABLE dbo.OrderDate (
Dates VARCHAR(20),
---IsNonOrder BIT,
EntityID VARCHAR(10),
EntityspecificID INT,
IsLock BIT
,TotalAmount AS
(dbo.FNGetTotalAmountbyEntity(EntityID,EntityspecificID,Dates)) )'
END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OrderDate' AND
type = 'U')
BEGIN
DROP TABLE dbo.OrderDate
END
exec (@SQLStatement)

IF (@@ERROR <>0)
BEGIN
DROP TABLE dbo.OrderDate
END
ELSE
BEGIN
WHILE (@StartDate <= @EndDate)
BEGIN
INSERT INTO dbo.OrderDate (
Dates,
EntityID,
EntityspecificID,
--IsNonOrder,
IsLock
)
SELECT convert(varchar(20),@StartDate,110),
@EntityID,
@EntityspecificID,
--dbo.IsNonDate(dbo.FNGetEntityIDbyAlias(@EntityID),@EntityspecificID,@StartDate),
dbo.IsDateLocked(dbo.FNGetEntityIDbyAlias(@EntityID),@EntityspecificID,@StartDate,getdate())

SET @StartDate = dateadd(day,1,@StartDate)
--print @StartDate
END

SELECT *
FROM
OrderDate
WHERE
dbo.IsNonDate(dbo.FNGetEntityIDbyAlias(EntityID),EntityspecificID,Dates) = 0
--where IsNonOrder = 0
AND (DATEPART(DW,Dates)) NOT IN (1,7)

DROP TABLE dbo.OrderDate
END
GO

"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message
news:eOva32oXGHA.4484@xxxxxxxxxxxxxxxxxxxxxxx
Can you post the actual code?

--
Andrew J. Kelly SQL MVP


"Rogers" <naissani@xxxxxxxxxxx> wrote in message
news:uGPf4ZmXGHA.3704@xxxxxxxxxxxxxxxxxxxxxxx
Is there any one let me know, in the stored procedure I am creating
temprary table and droping table at the end, when single user access it
is working fine but when simultaneous user access of that stored
procedure it giving error like #Temp table is not exists...

any idea ?

Thanks





.

Loading