这是我的存储过程 [英] this is my stored procedure
问题描述
CREATE PROCEDURE [dbo]。[USP_MaintenanceCompletionReport_PRACICE1]
@ WhereClause NVARCHAR (max)= NULL
AS
BEGIN
DECLARE @ SelectStatement NVARCHAR ( 2000 )
DECLARE @ FullStatement NVARCHAR ( 4000 )
DECLARE @ VesselPosition varchar ( 60 )
DECLARE @ ResponsibleName varchar ( 60 )
DECLARE @ ResponsibleId varchar ( 60 )
DECLARE @ FrequencyName varchar ( 60 )
DECLARE @ FrequencyId varchar ( 60 )
DECLARE @ TypeName varchar ( 60 )
DECLARE @ TypeId varchar ( 60 )
- DECLARE @VesselPosition varchar(60)
- DECLARE @VesselPosition varchar(60)
DECLARE @VesselPositionId varchar ( 60 )
- DECLARE @@ VesselPosition VARCHAR(60)
- SET @ VesselPositionId = 400
- 从dbo.PMS_TBL_EUIPMENT_VESSELPOSITION_MASTER $ b中选择@VesselPosition = VesselPosition
- $ b - 其中VesselPositionId = @ VesselPositionId
SET @ ResponsibleId = 400
选择 @ ResponsibleName = ResponsibleName
来自 dbo.PMS_TBL_RESPONSIBILITY_MASTER
其中 ResponsibleId = @ ResponsibleId
SET @ FrequencyId = 400
选择 @ FrequencyName = FrequencyName
来自 dbo.PMS_TBL_FREQUENCY_MASTER
其中 FrequencyId = @ FrequencyId
SET @ TypeId = 400
选择 < span class =code-sdkkeyword> @ TypeName = TypeName
来自 dbo.PMS _TBL_MAINTANANCETYPE_MASTER
其中 TypeId = @ TypeId
- SELECT CASE
- WHEN @TypeName为null然后''
- ELSE @TypeName
- END
- SELECT CASE
- 当@FrequencyName为null然后''
- ELSE @FrequencyName
- END
- SELECT CASE
- WHEN @ResponsibleName为null然后''
- ELSE @ResponsibleName
- END
- SELECT CASE
- 当@VesselPosition为空时,然后''
- - ELSE @VesselPosition
- 结束
SET @ SelectStatement = ' SELECT ROW_NUMBER()OVER(ORDER BY M.EquipmentId)AS Row,
dbo.getEquipmentName(M .EquipmentId)EquipmentName,
MaintananceId,
--dbo.getVesselpositi on(E.PositionInVessal)PositionInVessal,
CASE
WHEN @VesselPosition为null然后''
ELSE @VesselPosition
END AS PositionInVessal,
ClassCode,
MaintananceName,
CASE
WHEN @TypeName为null然后''
ELSE @TypeName
END - dbo.getMaintananceType(MaintanaceType)MaintanaceType,
--dbo.getFrequecyType (FrequencyType)FrequencyType,
CONVERT(VARCHAR(10),M.LastDoneDate,105)LastDoneDate,
LastDoneRunningHours,
频率,
CONVERT(VARCHAR(10) ,NextDueDate,105)NextDueDate,
NextDueRunningHours,
CASE
WHEN @ResponsibleName为null然后''
ELSE @ResponsibleName
END责任,CASE
WHEN @FrequencyName为空,然后''
ELSE @FrequencyName
END作为FrequencyType
FROM dbo.PMS_TBL_MAINTANANCE_MASTER M
INNER JOIN PMS_TBL_MAINTANANCE_COMPLETION C
ON C.MaintenanceId = M.MaintananceId
LEFT OUTER JOIN PMS_TBL_EQ UIPMENT_MASTER E
ON M.EquipmentId = convert(varchar(36),E.UniqueId)
LEFT OUTER JOIN PMS_TBL_SUB_EQUIPMENT_MASTER S
ON M.EquipmentId = convert(varchar(36),S.UniqueId )'
SET @ FullStatement = @ SelectStatement + ISNULL( @ WhereClause ,' ')
PRINT @ FullStatement
EXECUTE sp_executesql @ FullStatement
结束
当我执行它时,它收到错误,如消息137,级别15,状态2,行6必须声明标量变量 @VesselPosition 任何人都可以建议我
你在动态SQL中使用变量@VesselPosition
SET @SelectStatement ='SELECT ROW_NUMBER()OVER(ORDER BY M.EquipmentId)AS Row,
dbo.getEquipmentName(M.EquipmentId)EquipmentName,
MaintananceId,
CASE
WHEN @VesselPosition为null然后''
ELSE @VesselPosition
END AS PositionInVessal,
ClassCode,
MaintananceName,
CASE
< b> WHEN @TypeName为null然后''
ELSE @TypeName
END
CONVERT(VARCHAR(10),M.LastDoneDate,105)LastDoneDate,
LastDoneRunningHours ,
频率,
CONVERT(VARCHAR(10),NextDueDate,105)NextDueDate,
NextDueRunningHours,
CASE
WHEN @ResponsibleName为null然后''
ELSE @ResponsibleName
END责任,CASE
WHE @FrequencyName为null然后''
ELSE @FrequencyName
END作为FrequencyType
FROM dbo.PMS_TBL_MAINTANANCE_MASTER M
INNER JOIN PMS_TBL_MAINTANANCE_COMPLETION C
ON C.MaintenanceId = M.MaintananceId
LEFT OUTER JOIN PMS_TBL_EQUIPMENT_MASTER E
ON M.EquipmentId = convert(varchar(36),E.UniqueId)
LEFT OUTER JOIN PMS_TBL_SUB_EQUIPMENT_MASTER S
ON M.EquipmentId = convert(varchar(36),S.UniqueId)'/ / pre>
假设这些变量包含列名,那么您需要将实际值插入到sql中。您还需要了解ISNULL功能!
例如SET @ SelectStatement = ' SELECT ROW_NUMBER()OVER(ORDER BY M.EquipmentId)AS Row,
dbo.getEquipmentName(M.EquipmentId)EquipmentName,
MaintananceId,
ISNULL(' + @ VesselPosition + ' ,'''')AS PositionInVessal,
ClassCode,
MaintananceName,
ISNULL(' + < span class =code-sdkkeyword> @ TypeName + ' ,'''')AS TypeName
CONVERT(VARCHAR(10),M.LastDoneDate,105)LastDoneDate,
LastDoneRunningHours,
频率,
CONVERT(VARCHAR(10),NextDueDate,105)NextDueDate ,
NextDueRunningHours,
ISNULL(' + @ ResponsibleName + ' ,'''')责任,
ISNULL(' + @ FrequencyName + ' ,''')为FrequencyType
来自dbo.PMS_TBL_MAINTANANCE_MASTER M
INNER JOIN PMS_TBL_MAINTANANCE_COMPLETION C ON C.MaintenanceId = M.MaintananceId
LEFT OUTER JOIN PMS_TBL_EQUIPMENT_MASTER E ON M.EquipmentId = convert(varchar(36),E.UniqueId )
LEFT OUTER JOIN PMS_TBL_SUB_EQUIPMENT_MASTER S ON M.EquipmentId = convert(varchar(36),S.UniqueId)'
根据我的评论在早期的帖子中你不应该把日期真的转换为字符 - 这是一个演示文稿。
找到列名的方法看起来有点奇怪 - 如果你有硬编码的ids为什么有变量?无论如何你应该检查这些变量实际上是否获得一个值,否则整个@SelectStatement将为NULL。
CREATE PROCEDURE [dbo].[USP_MaintenanceCompletionReport_PRACICE1]
@WhereClause NVARCHAR(max) = NULL
AS
BEGIN
DECLARE @SelectStatement NVARCHAR(2000)
DECLARE @FullStatement NVARCHAR(4000)
DECLARE @VesselPosition varchar(60)
DECLARE @ResponsibleName varchar(60)
DECLARE @ResponsibleId varchar(60)
DECLARE @FrequencyName varchar(60)
DECLARE @FrequencyId varchar(60)
DECLARE @TypeName varchar(60)
DECLARE @TypeId varchar(60)
--DECLARE @VesselPosition varchar(60)
--DECLARE @VesselPosition varchar(60)
DECLARE @VesselPositionId varchar(60)
--DECLARE @@VesselPosition VARCHAR(60)
--SET @VesselPositionId=400
--select @VesselPosition = VesselPosition
-- from dbo.PMS_TBL_EUIPMENT_VESSELPOSITION_MASTER
-- where VesselPositionId=@VesselPositionId
SET @ResponsibleId=400
select @ResponsibleName = ResponsibleName
from dbo.PMS_TBL_RESPONSIBILITY_MASTER
where ResponsibleId=@ResponsibleId
SET @FrequencyId=400
select @FrequencyName = FrequencyName
from dbo.PMS_TBL_FREQUENCY_MASTER
where FrequencyId=@FrequencyId
SET @TypeId=400
select @TypeName = TypeName
from dbo.PMS_TBL_MAINTANANCETYPE_MASTER
where TypeId=@TypeId
--SELECT CASE
--WHEN @TypeName IS null then ''
-- ELSE @TypeName
--END
--SELECT CASE
--WHEN @FrequencyName IS null then ''
-- ELSE @FrequencyName
--END
--SELECT CASE
--WHEN @ResponsibleName IS null then ''
-- ELSE @ResponsibleName
--END
--SELECT CASE
--WHEN @VesselPosition IS null then ''
-- ELSE @VesselPosition
--END
SET @SelectStatement = 'SELECT ROW_NUMBER()OVER (ORDER BY M.EquipmentId) AS Row,
dbo.getEquipmentName(M.EquipmentId)EquipmentName,
MaintananceId,
--dbo.getVesselposition(E.PositionInVessal)PositionInVessal,
CASE
WHEN @VesselPosition IS null then ''
ELSE @VesselPosition
END AS PositionInVessal,
ClassCode,
MaintananceName,
CASE
WHEN @TypeName IS null then ''
ELSE @TypeName
END--dbo.getMaintananceType(MaintanaceType)MaintanaceType,
--dbo.getFrequecyType(FrequencyType)FrequencyType,
CONVERT(VARCHAR(10), M.LastDoneDate, 105)LastDoneDate,
LastDoneRunningHours,
Frequency,
CONVERT(VARCHAR(10), NextDueDate, 105) NextDueDate,
NextDueRunningHours,
CASE
WHEN @ResponsibleName IS null then ''
ELSE @ResponsibleName
END Responsibility,CASE
WHEN @FrequencyName IS null then ''
ELSE @FrequencyName
END as FrequencyType
FROM dbo.PMS_TBL_MAINTANANCE_MASTER M
INNER JOIN PMS_TBL_MAINTANANCE_COMPLETION C
ON C.MaintenanceId=M.MaintananceId
LEFT OUTER JOIN PMS_TBL_EQUIPMENT_MASTER E
ON M.EquipmentId=convert(varchar(36),E.UniqueId)
LEFT OUTER JOIN PMS_TBL_SUB_EQUIPMENT_MASTER S
ON M.EquipmentId=convert(varchar(36),S.UniqueId)'
SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')
PRINT @FullStatement
EXECUTE sp_executesql @FullStatement
END
when i execute it , it getting an error like " Msg 137, Level 15, State 2, Line 6 Must declare the scalar variable "@VesselPosition"." can any one suggest me the reason
You are using the variable @VesselPosition within your dynamic SQL
SET @SelectStatement = 'SELECT ROW_NUMBER()OVER (ORDER BY M.EquipmentId) AS Row, dbo.getEquipmentName(M.EquipmentId)EquipmentName, MaintananceId, CASE WHEN @VesselPosition IS null then '' ELSE @VesselPosition END AS PositionInVessal, ClassCode, MaintananceName, CASE WHEN @TypeName IS null then '' ELSE @TypeName END CONVERT(VARCHAR(10), M.LastDoneDate, 105)LastDoneDate, LastDoneRunningHours, Frequency, CONVERT(VARCHAR(10), NextDueDate, 105) NextDueDate, NextDueRunningHours, CASE WHEN @ResponsibleName IS null then '' ELSE @ResponsibleName END Responsibility,CASE WHEN @FrequencyName IS null then '' ELSE @FrequencyName END as FrequencyType FROM dbo.PMS_TBL_MAINTANANCE_MASTER M INNER JOIN PMS_TBL_MAINTANANCE_COMPLETION C ON C.MaintenanceId=M.MaintananceId LEFT OUTER JOIN PMS_TBL_EQUIPMENT_MASTER E ON M.EquipmentId=convert(varchar(36),E.UniqueId) LEFT OUTER JOIN PMS_TBL_SUB_EQUIPMENT_MASTER S ON M.EquipmentId=convert(varchar(36),S.UniqueId)'
Assuming those variables are containing column names then you need to insert the actual values into the sql. You also need to learn about the ISNULL function!
E.g.SET @SelectStatement = 'SELECT ROW_NUMBER() OVER (ORDER BY M.EquipmentId) AS Row, dbo.getEquipmentName(M.EquipmentId)EquipmentName, MaintananceId, ISNULL(' + @VesselPosition + ', '''') AS PositionInVessal, ClassCode, MaintananceName, ISNULL(' + @TypeName + ', '''') AS TypeName CONVERT(VARCHAR(10), M.LastDoneDate, 105)LastDoneDate, LastDoneRunningHours, Frequency, CONVERT(VARCHAR(10), NextDueDate, 105) NextDueDate, NextDueRunningHours, ISNULL(' + @ResponsibleName + ', '''') Responsibility, ISNULL(' + @FrequencyName + ','''') as FrequencyType FROM dbo.PMS_TBL_MAINTANANCE_MASTER M INNER JOIN PMS_TBL_MAINTANANCE_COMPLETION C ON C.MaintenanceId=M.MaintananceId LEFT OUTER JOIN PMS_TBL_EQUIPMENT_MASTER E ON M.EquipmentId=convert(varchar(36),E.UniqueId) LEFT OUTER JOIN PMS_TBL_SUB_EQUIPMENT_MASTER S ON M.EquipmentId=convert(varchar(36),S.UniqueId)'
As per my comments on earlier posts you shouldn't be converting dates to chars really - that's a presentation thing.
The means of finding the column names looks a little strange - if you have hard-coded ids why have the variables? In any event you should be checking for these variables actually getting a value otherwise the whole @SelectStatement will be NULL.
这篇关于这是我的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!