* 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
------------------------------------------------------
------------------------------------------------------