具有可变表名的动态SQL(重新发布) [英] Dynamic SQL with variable tablenames (Repost)

问题描述

我有一个存储过程，该存储过程接受参数，当这些参数与SQL语句的基本部分连接时，将生成所需的查询，然后我将动态执行该查询.

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  

插入表是可行的，但是后来我发现我无法使用传递参数以创建其余的动态SQL.

1.我正在寻找正确的方向来解决这个问题.
2.如果可以，我该如何进行?
3.如果没有任何减少处理时间的想法，将不胜感激.

在此先感谢

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

推荐答案

感谢大家的帮助.

我设法解决了这个问题.我没有为每个用户创建临时表，而是创建了一个永久表，并将变量用于其他动态sql查询中的表名.到目前为止，它似乎运行良好.当然，我最终在服务器中得到了冗余数据.为了解决这个问题，我在截断了客户表后直接运行了另一个proc.

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.

这篇关于具有可变表名的动态SQL(重新发布)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！