这是我的存储过程

本文介绍了这是我的存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 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 选择 @ 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 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（' + @ 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=@VesselPositionIdSET @ResponsibleId=400select @ResponsibleName = ResponsibleName from dbo.PMS_TBL_RESPONSIBILITY_MASTER where ResponsibleId=@ResponsibleId SET @FrequencyId=400select @FrequencyName = FrequencyName from dbo.PMS_TBL_FREQUENCY_MASTER where FrequencyId=@FrequencyId SET @TypeId=400select @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 @VesselPositionEND AS PositionInVessal, ClassCode, MaintananceName, CASE WHEN @TypeName IS null then ''ELSE @TypeNameEND--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 @ResponsibleNameEND Responsibility,CASEWHEN @FrequencyName IS null then ''ELSE @FrequencyNameEND as FrequencyTypeFROM dbo.PMS_TBL_MAINTANANCE_MASTER MINNER 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 ENDwhen 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 @VesselPositionEND AS PositionInVessal, ClassCode, MaintananceName, CASE WHEN @TypeName IS null then ''ELSE @TypeNameEND CONVERT(VARCHAR(10), M.LastDoneDate, 105)LastDoneDate,LastDoneRunningHours,Frequency,CONVERT(VARCHAR(10), NextDueDate, 105) NextDueDate,NextDueRunningHours, CASE WHEN @ResponsibleName IS null then ''ELSE @ResponsibleNameEND Responsibility,CASEWHEN @FrequencyName IS null then ''ELSE @FrequencyNameEND as FrequencyTypeFROM dbo.PMS_TBL_MAINTANANCE_MASTER MINNER 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. 这篇关于这是我的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！