Hi,
I have a stored procedure that accepts parameters which when concatenated with the basic part of an SQL statement produce the required query, which I then execute dynamically.
CREATE PROCEDURE [dbo].[ProcBookingDetails]
@Clerk varchar(max),
@DepDateFrom varchar(50),
@DepDateTo varchar(50),
@BookDateFrom varchar(50),
@BookDateTo varchar(50),
@Branches varchar(max),
@Products varchar(max),
@Suppliers varchar (max),
@Source varchar(max)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SET @sql =
'select distinct b.booking_number as BkgRef....'
set @SQL = @SQL + @BookDateFrom + @BookDateTo + @DepDateFrom + @DepDateTo + @Clerk + @Branches + @Suppliers + @Products + @Source
Create table #bkgnos (bkgref varchar(20))
insert into #bkgnos (bkgref)
EXECUTE sp_executesql @sql
This works fine but when there are multiple users on the system the queries are queued as they use the same temp table.
I was thinking to create a table/temp table for each user and enter the tablename dynamically to reduce the problem.
set @SQL1='IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@tablename+']'') AND type in (N''U''))
DROP TABLE [dbo].['+@tablename+']'
execute sp_executesql @SQL1
set @SQL1='Create table '+@tablename +' (bkgref varchar(20))'
execute sp_executesql @SQL1
Inserting the tables works but then I found that I could not use the passed in parameters to create the rest of the dynamic SQL.
1. I am looking in the right direction to solve this issue.
2. If so how do i proceed?
3. If not any ideas to reduce processing time will be appreciated.
Thanks in advance
Submit an article or tip