sql server中exec sp_executesql 的使用问题？

Question

题目描述

有一段SQL语句，我把 sql 单独拿出赋值使用，可以使用条件查询；放在一起使用时条件查询没有效果

题目来源及自己的思路

相关代码

完整SQL：

DECLARE @doctype VARCHAR(64)
DECLARE @sql Nvarchar(1000)
DECLARE @sql1 Nvarchar(1000)
DECLARE @BeginTime NVARCHAR(50)='2023-11-16' -- 开始时间
DECLARE @EndTime NVARCHAR(50)='2023-11-20' -- 结束时间
DECLARE @ThitObjectId NVARCHAR(100) -- 所属零件代号
select @doctype =oid from pdmdoctype where name='BOM表'
set @ThitObjectId='Z06-FZWCCST15-03'
set @sql='select p.ThitObjectId as 图号,p.filename as 名称,s.name as 录入人员,p.doctype as 文件类型,p.currevision as 文件版本,p.createtime 创建时间,l.name as 所属零件名称,l.ThitObjectId as 所属零件代号,l.cWorkCenter as 工作中心,l.beizhu as 备注 from pdmDocmaster p,Persons s,PartToDoc d,PartRevision l where d.DocId=p.oid and p.creater=s.id and l.id=d.PartId and p.currevision=d.DocVersion and l.version=d.PartVersion  and p.oid in(select oid from '+@doctype+' where createtime>= @BeginTime and createtime<=@EndTime and version=1) and right(p.filename,3)<> ''pdf'' 
union
select p.ThitObjectId as 图号,p.filename as 名称,s.name as 录入人员,p.doctype as 文件类型,p.currevision as 文件版本,p.createtime 创建时间,l.name as 所属零件名称,l.ThitObjectId as 所属零件代号,l.cWorkCenter as 工作中心,l.beizhu as 备注 from pdmDocmaster p,Persons s,TiPDMDocToDoc d,PartRevision l where d.SubDocId=p.oid and p.creater=s.id and l.id=d.SuperDocId and p.currevision=d.SubDocVer and l.version=d.SuperDocVer and p.oid in(select oid from '+@doctype+' where createtime>= @BeginTime and createtime<=@EndTime and version=1) and right(p.filename,3)<> ''pdf'' and l.ThitObjectId=@ThitObjectId
'
exec sp_executesql @sql,N'@BeginTime NVARCHAR(50), @EndTime NVARCHAR(50), @ThitObjectId NVARCHAR(100)', @BeginTime, @EndTime, @ThitObjectId

单独使用的SQL：

select p.ThitObjectId as 图号,p.filename as 名称,s.name as 录入人员,p.doctype as 文件类型,p.currevision as 文件版本,p.createtime 创建时间,l.name as 所属零件名称,l.ThitObjectId as 所属零件代号,l.cWorkCenter as 工作中心,l.beizhu as 备注 from pdmDocmaster p,Persons s,PartToDoc d,PartRevision l where d.DocId=p.oid and p.creater=s.id and l.id=d.PartId and p.currevision=d.DocVersion and l.version=d.PartVersion  and p.oid in(select oid from [BOM表] where createtime>= '2023-11-16' and createtime<='2023-11-20' and version=1) and right(p.filename,3)<> 'pdf'
union
select p.ThitObjectId as 图号,p.filename as 名称,s.name as 录入人员,p.doctype as 文件类型,p.currevision as 文件版本,p.createtime 创建时间,l.name as 所属零件名称,l.ThitObjectId as 所属零件代号,l.cWorkCenter as 工作中心,l.beizhu as 备注 from pdmDocmaster p,Persons s,TiPDMDocToDoc d,PartRevision l where d.SubDocId=p.oid and p.creater=s.id and l.id=d.SuperDocId and p.currevision=d.SubDocVer and l.version=d.SuperDocVer and p.oid in(select oid from [BOM表] where createtime>= '2023-11-16' and createtime<='2023-11-20' and version=1) and right(p.filename,3)<> 'pdf' and l.ThitObjectId='Z06-FZWCCST15-03'

你期待的结果是什么？实际看到的错误信息又是什么？

这是什么原因？？？求大神解答

Answer 1

DECLARE @doctype VARCHAR(64);
SELECT @doctype = oid FROM pdmdoctype WHERE name = 'BOM表';
DECLARE @sql NVARCHAR(1000);
SET @sql = '... where createtime >= ''' + @BeginTime + ''' and createtime <= ''' + @EndTime + ''' and version=1 ...';