Click here to Skip to main content
14,428,118 members
Rate this:
Please Sign up or sign in to vote.
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
Posted
Updated 10-Jun-11 12:53pm
v2
Comments
[no name] 3-Jun-11 21:49pm
   
Do you need the execute in 'EXECUTE sp_executesql @sql'?
Isn't 'sp_executesql @sql' enough?
[no name] 3-Jun-11 21:57pm
   
Why are you using dynamic sql and temp tables? Isn't there an alternative?

You can add the parameters and read results directly:

declare @bkgref varchar(20)

select distinct @bkgref = b.booking_number ...
milenalukic 5-Jun-11 15:28pm
   
I am building a system where the user queries on multiple fields, which would be straight forward if there was one criteria per field. The problem is that for each field the user can select all, one or many options so the query needs to change accordingly. What I've done so far is to have the basic script for the dynamic SQL in the sp and add the where clauses according to the selections.(ie nothing if they select all, where field=selection for one item selected or where field in (multiple selections)). Then I append all these to the initial script for the dynamic sql and run it.

This gives me the bookings we are interested in into a temptable and then I run the required query on these bookings. I have a number of different procs working in the same way some with a cursor over each booking, so it is quite complex.

This second part of the query saves to another temp table from which then I select the required data to present to the user (eg totals per supplier etc).

For most cases I get results in a few minutes which is great until I have a user who requires a large data set and gets all the others queuing to use the temp tables. I want to avaiod this queue and maximise cpu usage. That is why I was trying to create temp tables for each user. The problem I cannot work round is that SQL doesn't carry forward parameters into dynamic SQL.

Any ideas please?
hitesh_tech 10-Jun-11 7:19am
   
If user select all then what will be sample query formed, could you please
mention.
R. Giskard Reventlov 11-Jun-11 1:46am
   
Have you tried this with a table variable instead? declare @temp table (columns)

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Thanks to all for your help.

I managed to work around the issue. Instead of creating a temp table for each user I created a permanent table and used the variable for the tablename inside the other dynamic sql queries. It seems to be working fine so far. Of course I end up with redundant data in the server. To solve this I run another proc straight after truncating the client table.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100