动态SQL中的静态参数 [英] Static parameters in Dynamic SQL

问题描述

我有一个存储过程，该存储过程接受参数，当这些参数与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
<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

插入表是可行的，但是随后我发现我无法使用传入的参数来创建动态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

推荐答案

可以让您知道如何创建变量@tablename.
无论如何，创建临时表可能会影响性能.检查是否也可以避免使用临时表.

Can you let you know how you are creating the variable @tablename.
Creating temp tables can affect performance anyway. Check if you can avoid temptable also.

这篇关于动态SQL中的静态参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！