Static parameters in Dynamic SQL

See more:

SQL-Server-2005

SQL-Server

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
<pre lang="sql">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