sql - 存储过程在使用 UNION 从两个表中获取数据时引发错误

Question

我有两个表，显示在这个屏幕截图中:

我正在编写一个将从两个表返回数据的存储过程:

ALTER PROCEDURE [dbo].[GetInventoryDetails] 
    @MaterialId INT
AS
BEGIN
    SELECT 
        tms.Material_ID AS MaterialId,
        tmm.Name As MaterialName,
        CONVERT(varchar,Quantity) AS AddedQuantity, 
        UtilizedQuantity ='-',
        tcl.LedgerName AS SupplierName,
        UsedFor = '-',
        tmm.CurrentStock,
        tmm.OpeningStock,
        CONVERT(DATETIME,CONVERT(VARCHAR(100), tms.Material_Date, 112)) AS MaterialDate,
        tms.Narration AS Narration
    FROM 
        tblMaterialSheet tms 
    JOIN 
        tblMaterialMaster tmm ON tmm.Material_ID = tms.Material_ID
    JOIN  
        tblCompanyLedger tcl ON tcl.Pk_LedgerId = tms.Ledger_ID
    WHERE 
        tms.Material_ID = @MaterialId 
        AND tms.isActive = 1

UNION 

SELECT 
    tmu.Material_ID AS MaterialId,
    tmm.Name As MaterialName,
    AddedQuantity = '-',
    CONVERT(varchar,Utilized_Quantity) AS UtilizedQuantity,
    CONVERT(DATETIME,CONVERT(VARCHAR(100), Utilization_Date, 112)) AS MaterialDate,
    SupplierName = '-',
    tbst.Name AS UsedFor,
    tmm.CurrentStock,
    tmm.OpeningStock,
    tmu.Narration As Narration
FROM 
    tblMaterialUtilization tmu 
JOIN 
    tblMaterialMaster tmm ON tmm.Material_ID = tmu.Material_ID
JOIN 
    tblBuildingSubTask tbst ON tbst.BuildingSubTask_ID = tmu.BuildingSubTask_ID
WHERE 
    tmu.Material_ID = @MaterialId
    AND tmu.isActive = 1
END 

当我调用存储过程时，它抛出一个错误:

Conversion failed when converting date and/or time from character string.

表结构:tblmaterialsheet

CREATE TABLE [dbo].[tblMaterialSheet]
(
    [MaterialSheet_ID] [int] IDENTITY(1,1) NOT NULL,
    [Company_ID] [int] NOT NULL,
    [User_ID] [int] NOT NULL,
    [BuildingSubTask_ID] [int] NOT NULL,
    [Material_Date] [datetime] NOT NULL,
    [Material_ID] [int] NOT NULL,
    [Unit_ID] [int] NOT NULL,
    [Quantity] [decimal](10, 2) NOT NULL,
    [Size_ID] [int] NULL,
    [Height] [decimal](6, 2) NULL,
    [Width] [decimal](6, 2) NULL,
    [Rate_Per_Unit] [money] NULL,
    [Paid_Amount] [money] NULL,
    [Total_Amount] [money] NULL,
    [Vehical_No] [varchar](50) NULL,
    [Ledger_ID] [int] NULL,
    [Narration] [varchar](max) NULL,
    [Challan_No] [int] NULL,
    [Bill_ID] [int] NULL,
    [isBilled] [bit] NOT NULL,
    [Approval] [varchar](50) NULL,
    [Approval_ModifiedDate] [datetime] NULL,
    [UploadImage] [image] NULL,
    [isActive] [bit] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [ModifiedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL
)

表结构:tblMaterialUtilization

CREATE TABLE [dbo].[tblMaterialUtilization]
(
    [MaterialUtilization_ID] [int] IDENTITY(1,1) NOT NULL,
    [Company_ID] [int] NOT NULL,
    [User_ID] [int] NOT NULL,
    [BuildingSubTask_ID] [int] NOT NULL,
    [Material_ID] [int] NOT NULL,
    [Utilization_Date] [datetime] NOT NULL,
    [Utilized_Quantity] [decimal](10, 2) NOT NULL,
    [Narration] [varchar](max) NOT NULL,
    [IsActive] [bit] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [ModifiedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL
)

表结构:tblMaterialMaster

CREATE TABLE [dbo].[tblMaterialMaster]
(
    [Material_ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [Unit_ID] [int] NOT NULL,
    [IsActive] [bit] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [ModifiedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,
    [OpeningStock] [numeric](18, 0) NULL,
    [PurchaseLedger] [numeric](18, 0) NULL,
    [CurrentStock] [numeric](18, 0) NULL
)

表结构:tblBuildingSubTask

CREATE TABLE [dbo].[tblBuildingSubTask]
(
    [BuildingSubTask_ID] [int] IDENTITY(1,1) NOT NULL,
    [BuildingTask_ID] [int] NOT NULL,
    [Name] [varchar](200) NOT NULL,
    [Narration] [varchar](max) NULL,
    [StartDate] [datetime] NULL,
    [TargetCompletionDate] [datetime] NULL,
    [ActualCompletionDate] [datetime] NULL,
    [IsActive] [bit] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [ModifiedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL
)

如何解决这个错误？

Answer 1

现在试试这个:列的顺序不相同，因此它为同一列获取不同的数据类型值。 UNION中Datatype和Order最重要

ALTER PROCEDURE [dbo].[GetInventoryDetails] 
@MaterialId int
AS
BEGIN
    SELECT 
    tms.Material_ID AS MaterialId,
    tmm.Name As MaterialName,
    CONVERT(varchar,Quantity) AS AddedQuantity, 
    UtilizedQuantity ='-',
    tcl.LedgerName AS SupplierName,
    UsedFor='-',
    tmm.CurrentStock,
    tmm.OpeningStock,
    CONVERT(DATETIME,CONVERT(VARCHAR(100), tms.Material_Date, 112)) AS MaterialDate,
    tms.Narration As Narration
        FROM 
            tblMaterialSheet tms 
            JOIN tblMaterialMaster tmm on tmm.Material_ID = tms.Material_ID
            JOIN tblCompanyLedger tcl on tcl.Pk_LedgerId = tms.Ledger_ID
        WHERE 
            tms.Material_ID = @MaterialId 
        AND 
            tms.isActive = 1
UNION 
    SELECT 
    tmu.Material_ID AS MaterialId,
    tmm.Name As MaterialName,
    AddedQuantity = '-',
    CONVERT(varchar,Utilized_Quantity) AS UtilizedQuantity,
    SupplierName = '-', --Moved up
    tbst.Name AS UsedFor, --Moved up
    tmm.CurrentStock, --Moved up
    tmm.OpeningStock, --Moved up
    CONVERT(DATETIME,CONVERT(VARCHAR(100), Utilization_Date, 112)) AS MaterialDate,
    tmu.Narration As Narration
        FROM 
            tblMaterialUtilization tmu 
            JOIN tblMaterialMaster tmm on tmm.Material_ID = tmu.Material_ID
            JOIN tblBuildingSubTask tbst on tbst.BuildingSubTask_ID = tmu.BuildingSubTask_ID
        WHERE 
            tmu.Material_ID = @MaterialId
        AND 
            tmu.isActive = 1
END