SQL SERVER存储过程执行返回Return Value=0，未输出预期结果

2019独角兽企业重金招聘Python工程师标准>>>

存储过程中我写了两条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

 

转载于:https://my.oschina.net/CrazyBoy1024/blog/825439