sql - 为什么将参数作为列名？
问题描述
我有这个动态查询
ALTER PROCEDURE [dbo].[GetEquipmentsAllData]
@Eq_IN VARCHAR(18) = ''
,@FromDate DATE = NULL
,@ToDate DATE = NULL
,@ServertableName VARCHAR(400)
AS
BEGIN
IF (ISNULL(@servertableName, '') = '')
SET @servertableName = '[Vail-PlantAdgas-Webservices]'
DECLARE @Sql NVARCHAR(max)
IF (@EQ_IN = '')
BEGIN
IF (
@FromDate IS NULL
AND @ToDate IS NULL
)
BEGIN
SET @Sql = 'Select
distinct H.EQHeaderID,H.EQUNR ''Equipment No'', H.STTXT ''Description'', H.EQTYP ''Equipment Type'',
--H.DATAB ''Valid From'',
g.EQART ''Object Type'', g.HERST ''Manufacturer'', g.TYPBZ ''Model No'',
g.SERGE ''Manufacturer Serial No'', g.HERLD ''Manufacturer Country'', g.BAUJJ ''Construction Year'', g.BAUMM ''Construction Month'',
loc.SWERK ''Maint Plant'', loc.STORT ''Location'', loc.BEBER ''Plant Section'',
Org.BUKRS ''Company Code'', Org.ANLNR ''Asset'', Org.GSBER ''Business Area'', Org.KOSTL ''Cost Center'', Org.PROID ''WBS Element'',
str.TPLNR ''Functional Location'', str.HEQNR ''Superior Equipment'', str.POSNR ''Position'', str.SUBMT ''Const Type''
from ' + @servertableName + '.dbo.EQ_Header h
left Join ' + @servertableName + '.dbo.EQ_General g
ON h.EQHeaderID= g.HeaderID
left Join ' + @servertableName +
'.dbo.EQ_Location loc
ON loc.HeaderID= h.EQHeaderID
left Join ' + @servertableName + '.dbo.EQ_Organizations org
ON org.HeaderID= loc.HeaderID
left Join ' + @servertableName + '.dbo.EQ_Structure str
ON str.HeaderID= org.HeaderID
--Left Join ' + @servertableName + '.dbo.Equipment eq
--ON eq.EquipmentNo= h.EQUNR
Where h.Status IS NULL OR Status NOT IN(1,3)'
END
ELSE
BEGIN
SET @Sql = 'Select
distinct H.EQHeaderID,H.EQUNR ''Equipment No'', H.STTXT ''Description'', H.EQTYP ''Equipment Type'',
--H.DATAB ''Valid From'',
g.EQART ''Object Type'', g.HERST ''Manufacturer'', g.TYPBZ ''Model No'',
g.SERGE ''Manufacturer Serial No'', g.HERLD ''Manufacturer Country'', g.BAUJJ ''Construction Year'', g.BAUMM ''Construction Month'',
loc.SWERK ''Maint Plant'', loc.STORT ''Location'', loc.BEBER ''Plant Section'',
Org.BUKRS ''Company Code'', Org.ANLNR ''Asset'', Org.GSBER ''Business Area'', Org.KOSTL ''Cost Center'', Org.PROID ''WBS Element'',
str.TPLNR ''Functional Location'', str.HEQNR ''Superior Equipment'', str.POSNR ''Position'', str.SUBMT ''Const Type''
from ' + @servertableName + '.dbo.EQ_Header h
left Join ' + @servertableName + '.dbo.EQ_General g
ON h.EQHeaderID= g.HeaderID
left Join ' + @servertableName +
'.dbo.EQ_Location loc
ON loc.HeaderID= h.EQHeaderID
left Join ' + @servertableName + '.dbo.EQ_Organizations org
ON org.HeaderID= loc.HeaderID
left Join ' + @servertableName + '.dbo.EQ_Structure str
ON str.HeaderID= org.HeaderID
--Left Join ' + @servertableName + '.dbo.Equipment eq
--ON eq.EquipmentNo= h.EQUNR
Where h.Status IS NULL OR Status NOT IN(1,3) and
h.EntryDateTime BETWEEN ''' + convert(NVARCHAR(20), @FromDate) + ''' AND ''' + convert(NVARCHAR(20), @ToDate) + ''''
END
END
ELSE
BEGIN
IF (
@FromDate IS NULL
AND @ToDate IS NULL
)
BEGIN
SET @Sql = 'Select
distinct H.EQHeaderID,H.EQUNR ''Equipment No'', H.STTXT ''Description'', H.EQTYP ''Equipment Type'',
--H.DATAB ''Valid From'',
g.EQART ''Object Type'', g.HERST ''Manufacturer'', g.TYPBZ ''Model No'',
g.SERGE ''Manufacturer Serial No'', g.HERLD ''Manufacturer Country'', g.BAUJJ ''Construction Year'', g.BAUMM ''Construction Month'',
loc.SWERK ''Maint Plant'', loc.STORT ''Location'', loc.BEBER ''Plant Section'',
Org.BUKRS ''Company Code'', Org.ANLNR ''Asset'', Org.GSBER ''Business Area'', Org.KOSTL ''Cost Center'', Org.PROID ''WBS Element'',
str.TPLNR ''Functional Location'', str.HEQNR ''Superior Equipment'', str.POSNR ''Position'', str.SUBMT ''Const Type''
from ' + @servertableName + '.dbo.EQ_Header h
left Join ' + @servertableName + '.dbo.EQ_General g
ON h.EQHeaderID= g.HeaderID
left Join ' + @servertableName +
'.dbo.EQ_Location loc
ON loc.HeaderID= h.EQHeaderID
left Join ' + @servertableName + '.dbo.EQ_Organizations org
ON org.HeaderID= loc.HeaderID
left Join ' + @servertableName + '.dbo.EQ_Structure str
ON str.HeaderID= org.HeaderID
--Left Join ' + @servertableName + '.dbo.Equipment eq
--ON eq.EquipmentNo= h.EQUNR
where h.EQUNR= ' + @Eq_IN + ' and h.Status IS NULL OR Status NOT IN(1,3)'
END
ELSE
BEGIN
SET @Sql = 'Select
distinct H.EQHeaderID,H.EQUNR ''Equipment No'', H.STTXT ''Description'', H.EQTYP ''Equipment Type'',
--H.DATAB ''Valid From'',
g.EQART ''Object Type'', g.HERST ''Manufacturer'', g.TYPBZ ''Model No'',
g.SERGE ''Manufacturer Serial No'', g.HERLD ''Manufacturer Country'', g.BAUJJ ''Construction Year'', g.BAUMM ''Construction Month'',
loc.SWERK ''Maint Plant'', loc.STORT ''Location'', loc.BEBER ''Plant Section'',
Org.BUKRS ''Company Code'', Org.ANLNR ''Asset'', Org.GSBER ''Business Area'', Org.KOSTL ''Cost Center'', Org.PROID ''WBS Element'',
str.TPLNR ''Functional Location'', str.HEQNR ''Superior Equipment'', str.POSNR ''Position'', str.SUBMT ''Const Type''
from ' + @servertableName + '.dbo.EQ_Header h
left Join ' + @servertableName + '.dbo.EQ_General g
ON h.EQHeaderID= g.HeaderID
left Join ' + @servertableName +
'.dbo.EQ_Location loc
ON loc.HeaderID= h.EQHeaderID
left Join ' + @servertableName + '.dbo.EQ_Organizations org
ON org.HeaderID= loc.HeaderID
left Join ' + @servertableName + '.dbo.EQ_Structure str
ON str.HeaderID= org.HeaderID
--Left Join ' + @servertableName + '.dbo.Equipment eq
--ON eq.EquipmentNo= h.EQUNR
where h.EQUNR= ' + @Eq_IN + '
and Cast(h.EntryDateTime as Date) BETWEEN ''' + convert(NVARCHAR(20), @FromDate) + ''' AND ''' + convert(NVARCHAR(20), @ToDate) + '''
and h.Status IS NULL OR Status NOT IN(1,3)'
END
END
--Exec(@Sql)
PRINT (@Sql)
END
打印出来是这样的：
Select
distinct H.EQHeaderID,H.EQUNR 'Equipment No', H.STTXT 'Description', H.EQTYP 'Equipment Type',
--H.DATAB 'Valid From',
g.EQART 'Object Type', g.HERST 'Manufacturer', g.TYPBZ 'Model No',
g.SERGE 'Manufacturer Serial No', g.HERLD 'Manufacturer Country', g.BAUJJ 'Construction Year', g.BAUMM 'Construction Month',
loc.SWERK 'Maint Plant', loc.STORT 'Location', loc.BEBER 'Plant Section',
Org.BUKRS 'Company Code', Org.ANLNR 'Asset', Org.GSBER 'Business Area', Org.KOSTL 'Cost Center', Org.PROID 'WBS Element',
str.TPLNR 'Functional Location', str.HEQNR 'Superior Equipment', str.POSNR 'Position', str.SUBMT 'Const Type'
from [Vail-PlantAdgas-Webservices].dbo.EQ_Header h
left Join [Vail-PlantAdgas-Webservices].dbo.EQ_General g
ON h.EQHeaderID= g.HeaderID
left Join [Vail-PlantAdgas-Webservices].dbo.EQ_Location loc
ON loc.HeaderID= h.EQHeaderID
left Join [Vail-PlantAdgas-Webservices].dbo.EQ_Organizations org
ON org.HeaderID= loc.HeaderID
left Join [Vail-PlantAdgas-Webservices].dbo.EQ_Structure str
ON str.HeaderID= org.HeaderID
--Left Join [Vail-PlantAdgas-Webservices].dbo.Equipment eq
--ON eq.EquipmentNo= h.EQUNR
where h.EQUNR= eq01
and Cast(h.EntryDateTime as Date) BETWEEN '2014-01-01' AND '2020-01-01'
and h.Status IS NULL OR Status NOT IN(1,3)
当我将参数传递给它时它会引发错误：
错误：列名“eq01”无效。
参数：'eq01','2014-01-01','2020-01-01',''
现在我已经尝试了很多方法来解决它，但不知道我错过了什么。
解决方案
您的代码中有两个此子句的实例：
where h.EQUNR= ' + @Eq_IN + '
该参数定义为 a VARCHAR(18)，因此当您在动态 SQL 中引用它时，您需要对其进行设置，以便在评估语句时将值包裹在单引号中。您可以通过在动态查询中将其用双单引号括起来来做到这一点。第一个单引号是第二个单引号的转义字符。
那么上面的实例应该是什么：
where h.EQUNR= ''' + @Eq_IN + '''
如果没有这些引号，文本将在您发布时被评估，其中eq01被视为列名，而不是文本值。
推荐阅读
- json - AdaptiveCardsJson - 点击通话选项而不打开新 URL
- php - 使用存储库模式编写 REST API 的函数 update() 出现问题
- python - 使用类自变量作为默认类方法参数
- cmd - 根据文件类型扩展名更改文件名
- microsoft-cognitive - HoloLens 上的 onnx 1.2 模型（自定义视觉）
- javascript - 如何解决字体截取问题？
- elasticsearch - 更像这样 - 弹性搜索没有给出正确的结果
- java - JAVA 中的 Hive JDBC 连接
- vue.js - 启动 VueJS 应用程序时 JavaScript 未启用错误
- javascript - 除了 v-toolbar 之外，还有其他方法可以修复 Vuetify 中的元素吗？