存储过程中我写了两条sql语句,正确执行应该会返回两个datatable出来。
但是执行存储过程时返回了如下结果:
之前没写过存储过程,请教同事后得知:“在Sql存储过程中定义变量,必须赋予初始值,否则为null”
将
改为:
再重新执行存储过程,正确返回了两个查询结果:
贴上存储过程代码
USE [Information]
GO
/****** Object: StoredProcedure [dbo].[P_ExtendCars_GetExtendCarAndSerialsList] Script Date: 2017/1/16 14:43:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <liuchangxin>
-- Create date: <2017年1月14日>
-- Description: <取出商家所销售的增配车信息列表和增配车品牌信息列表>
-- =============================================
ALTER PROCEDURE [dbo].[P_ExtendCars_GetExtendCarAndSerialsList]
-- Add the parameters for the stored procedure here
@DealerId INT ,
@SerialId INT ,
@ExtendCarName INT ,
@StartTime DATETIME ,
@EndTime DATETIME
AS
BEGIN
DECLARE @Sql NVARCHAR(MAX)='';
DECLARE @Where NVARCHAR(MAX)='';
SET @Where += ' WHERE t1.IsActive = 1 AND t4.IsActive=1 AND t1.DealerId=@DealerId'
IF @SerialID > 0
BEGIN
SET @Where += 'AND t1.SerialId=@SerialId'
END
IF @ExtendCarName != ''
BEGIN
SET @Where += ' AND t1.ExtendCarName=@ExtendCarName'
END
IF @StartTime != ''
BEGIN
SET @Where += ' AND t1.CreateTime>=@StartTime'
END
IF @EndTime != ''
BEGIN
SET @Where += ' AND t1.CreateTime <= @EndTime'
END
SET @sql += ' SELECT t1.ExtendCarId ,
t1.ExtendCarName,
t1.Price ,
t1.CreateTime ,
t2.CarID ,
t2.CarName ,
t3.SerialID ,
t3.SerialName ,
t4.BrandID ,
t4.BrandName ,
--t5.ParamItemID ,
--t5.ParamItemName ,
--t5.Price AS ParamPrice,
(SELECT SUM(Price) FROM dbo.DealerExtendCarIdParamRela WITH(NOLOCK)
WHERE ExtendCarId = t1.ExtendCarId
)AS SumItemPrice,
( SELECT ParamItemName + '' ''
FROM dbo.DealerExtendCarIdParamRela WITH ( NOLOCK )
WHERE ExtendCarId = t1.ExtendCarId
FOR
XML PATH('''')
) AS XmlItemName
INTO #TmpResult
FROM dbo.DealerExtendCarsInfo t1
INNER JOIN Base.dbo.CarBasic t2 WITH ( NOLOCK ) ON t2.CarID = t1.CarId
INNER JOIN Base.dbo.CarSerial t3 WITH ( NOLOCK ) ON t3.SerialID = t2.SerialID
INNER JOIN Base.dbo.CarBrand t4 WITH ( NOLOCK ) ON t4.BrandID = t3.BrandID
LEFT JOIN dbo.DealerExtendCarIdParamRela t5 WITH ( NOLOCK ) ON t1.ExtendCarId = t5.ExtendCarId
'
-- Insert statements for procedure here
SET @Sql += @Where
SET @Sql +=' GROUP BY t1.ExtendCarId ,
t1.ExtendCarName,
t1.Price ,
t1.CreateTime ,
t2.CarID ,
t2.CarName ,
t3.SerialID ,
t3.SerialName ,
t4.BrandID ,
--t5.ParamItemID
--t5.ParamItemName ,
--t5.Price
t4.BrandName '
SET @Sql += ' SELECT DISTINCT
SerialID ,
SerialName ,
BrandID ,
BrandName
FROM #TmpResult'
SET @Sql += ' SELECT * FROM #TmpResult'
SET @Sql += ' DROP TABLE #TmpResult'
PRINT @Sql
EXECUTE sys.sp_executesql @Sql, N' @DealerId INT ,
@SerialId INT ,
@ExtendCarName INT ,
@StartTime DATETIME ,
@EndTime DATETIME', @DealerId,
@SerialId, @ExtendCarName, @StartTime, @EndTime
END