Dynamic SQL with variable tablenames (Repost)

Question

2.50/5 (2 votes)

See more:

SQL-Server-2005

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.

SQL

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.

SQL

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 3-Jun-11 3:56am

milenalukic

Updated 10-Jun-11 11:53am

Maciej Los

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

milenalukic · Accepted Answer · 2011-06-14T09:47:00

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.