These are not temporary tables, they are real ones. Temporary tables always
begin with #. Why on earth are you dynamically creating real tables just to
insert some rows, select out of it and drop it again? If you need to do
that then create a real temp table (#). You can do that all in a dynamic
sql scope if you wrap all the code into the dynamic sql string. I think your
problem comes from these lines of code:
[quoted text, click to view] > 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
If @@ERROR was <> 0 then the table would never have been created in the
first place so you can't drop it.
But when it all comes down to it I don't see a reason to use tables at all.
Without looking too deep here I believe it can all be done with straight
select statements. If you need to dynamically create a column name then you
may have to use dynamic SQL but you should not need any intermediate tables.
--
Andrew J. Kelly SQL MVP
[quoted text, click to view] "Rogers" <naissani@hotmail.com> wrote in message
news:e9lAKzwXGHA.4120@TK2MSFTNGP03.phx.gbl...
>
> 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@shadhawk.com> wrote in message
> news:eOva32oXGHA.4484@TK2MSFTNGP02.phx.gbl...
>> Can you post the actual code?
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>>
>> "Rogers" <naissani@hotmail.com> wrote in message
>> news:uGPf4ZmXGHA.3704@TK2MSFTNGP03.phx.gbl...
>>> 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
>>>
>>
>>
>
>
