Cari Blog Ini

13 Desember 2013

How To Create Temporary Table in Multiple User

Apply To :
* Microsoft SQL Server 2008 R2

Problem :

* Want to create query or procedure for temporary table that use in multiple user
* Using syntax procedure in procedure again






Solution :

1. Example Table :




2. Example create procedure name "sp_Orders_CustomerID"

----------------------------------------------------------
----------------------------------------------------------
-----CODE START

create procedure sp_Orders_CustomerID
@CustomerID as varchar(20),
@User varchar(100)
as 

begin 

Declare @TableName_1  varchar(255),
@strSQL varchar(8000)

set @TableName_1=''
set @TableName_1='_'+@User+'_Temp_Orders_CustomerID'

if EXISTS(select * from INFORMATION_SCHEMA.tables where table_name = @TableName_1 ) 
begin exec('drop table '+@TableName_1) end


exec('
select * into '+@TableName_1+' from 

select OrderID, OrderDate, ShippedDate,Freight  
from [Orders] 
where CustomerID= '''+@CustomerID+'''
) an

')

end

-----CODE END
----------------------------------------------------------
----------------------------------------------------------






3. Example create procedure name "sp_View_Orders_Customer"

----------------------------------------------------------
----------------------------------------------------------
-----CODE START

create Procedure [dbo].[sp_View_Orders_Customer]
@CustomerID as varchar(20),
@User varchar(100)
as
begin 

Declare @TableName_1  varchar(255),
@TableName_2  varchar(255)

set @TableName_1=''
set @TableName_1='_'+@User+'_Temp_View_Orders_Customer'

set @TableName_2=''
set @TableName_2='_'+@User+'_Temp_Orders_CustomerID'


if EXISTS(select * from INFORMATION_SCHEMA.tables where table_name = @TableName_1) 
begin exec('drop table '+@TableName_1) end
if EXISTS(select * from INFORMATION_SCHEMA.tables where table_name = @TableName_2) 
begin exec('drop table '+@TableName_2) end


exec('
Create Table '+@TableName_1+'
(
OrderID int,
OrderDate smalldatetime,
ShippedDate smalldatetime,
Freight money
)
')

exec sp_Orders_CustomerID @CustomerID,@User

exec('
insert '+@TableName_1+'
select OrderID,
OrderDate,ShippedDate,Freight
from '+@TableName_2+'
')

if EXISTS(select * from INFORMATION_SCHEMA.tables where table_name = @TableName_2) 
begin exec('drop table '+@TableName_2) end

exec(' select * from '+@TableName_1+' ')

end

-----CODE END
----------------------------------------------------------
----------------------------------------------------------







4. The procedure already created.




5. Try to run the procedure, example for User_1

------------------------------------------------------
------------------------------------------------------
-----CODE START

exec [sp_View_Orders_Customer] 'WARTH','User_1'

-----CODE END
------------------------------------------------------
------------------------------------------------------





6. Try to run the procedure, example for User_2

------------------------------------------------------
------------------------------------------------------
-----CODE START

exec [sp_View_Orders_Customer] 'WARTH','User_2'

-----CODE END
------------------------------------------------------
------------------------------------------------------




7. Optional, to remove the procedure

------------------------------------------------------
------------------------------------------------------
-----CODE START

drop procedure [dbo].sp_View_Orders_Customer

drop procedure [dbo].sp_Orders_CustomerID

-----CODE END
------------------------------------------------------
------------------------------------------------------