这是我的存储过程 [英] 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屋！