在我的存储过程中尝试了我的级别最佳错误 [英] Tried my level best error in my stored procedure

问题描述

USE [HIMT_Testing]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo]。[CourseRankWiseSearch]

（

@Rank varchar（20），@ FromDate varchar （20），@ ToDate varchar（20），@ Type varchar（20）

）

as

begin

USE [HIMT_Testing]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[CourseRankWiseSearch]
(
@Rank varchar(20),@FromDate varchar(20),@ToDate varchar(20),@Type varchar(20)
)
as
begin

declare
@Course varchar(100),
@Code varchar(100),
@Descr varchar(max),
@Eligbility varchar(max),
@Days varchar(20),
@Startdt datetime,
@Enddt datetime
CREATE TABLE #TestTable5(
Course VARCHAR(100) NOT NULL
,Code varchar(100) Not NULL
,Description varchar(max)
,Days varchar(20)
,Startdt VARCHAR(50) NOT NULL
,Enddt VARCHAR(50) NOT NULL
)

insert into #TestTable5(Course,Code,Description,Days,Startdt,Enddt)Values(@Course,@Code,@Descr,@Days,@Startdt,@Enddt)

If @Type ='Rank'
BEGIN
select b.cmn_minor_code as Course,
f.cmj_major_desc as Code,
c.cmn_minor_desc as Description,
c.cmn_minor_day as Days,
convert(char(12),b.cbm_batch_start_dt,106) as Startdt,
convert(char(12),b.cbm_batch_end_dt,106) as Enddt
from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b,CO_MINOR_MASTER as c,CO_MAJOR_MASTER as f
where a.course = b.cmn_minor_code and a.Rank =@Rank and b.cbm_active <> 'd'
and b.cmn_minor_code = c.cmn_minor_code and f.cmj_major_code = b.cmj_major_code
and a.Active <> 'd' and b.cbm_batch_start_dt between @FromDate and @ToDate
order by b.cmn_minor_code asc
END

else if (@Type = 'Crs')
BEGIN
select b.cmn_minor_code as course,f.cmj_major_desc as Code,c.cmn_minor_desc as Description,c.cmn_minor_day as Days,convert(char(12),b.cbm_batch_start_dt,106) as startdt,
convert(char(12),b.cbm_batch_end_Dt,106) as Enddt from CO_BATCH_MASTER as b,CO_MINOR_MASTER as c,CO_MAJOR_MASTER as f
where b.cmn_minor_code LIKE '%' + @Rank + '%' and b.cbm_active <> 'd'
and b.cmn_minor_code = c.cmn_minor_code and f.cmj_major_code = b.cmj_major_code
and b.cbm_batch_start_dt between @FromDate and @ToDate
order by b.cmn_minor_code asc
end

SELECT
CASE WHEN RowNo =1 THEN [Course] ELSE '' END AS [Course],
CASE WHEN RowNo =1 THEN [Code] ELSE '' END AS [Code],
CASE WHEN RowNo =1 THEN [Description] ELSE '' END AS [Description],
CASE WHEN RowNo =1 THEN [Days] ELSE '' END AS [Days],
Startdt,Enddt
from (
SELECT Course,Code,Description,Days,Startdt,Enddt, ROW_NUMBER() OVER(PARTITION BY Course ORDER BY Course,Code,Description,Days,Startdt,Enddt) AS RowNo
FROM #TestTable5
) AS T
END
 

When i execute the stored procedure as follows
 
exec [CourseRankWiseSearch] 'SSO',   '2012-01-01',   '2012-12-30',  'crs' 
 
 The error occuras  follows
 
Cannot insert the value NULL into column 'Course', table 'tempdb.dbo.#TestTable5 00000000000F'; column does not allow nulls. INSERT fails.

What I have tried:

please help me what is the mistake in my abvoe stored procedure

推荐答案

查看错误消息：

Look at the error message:

Cannot insert the value NULL into column 'Course', table 'tempdb.dbo.#TestTable5 00000000000F'; column does not allow nulls. INSERT fails.

有两个明显的解决方案：

1）更改列定义，使其通过删除NOT接受空值NULL约束。

或

2）更改数据，使其不会尝试将NULL值传递给存储过程以进行插入。

There are two obvious solutions to this:
1) Change the column definition so it does accept null values by removing the NOT NULL constraint.
Or
2) Change the data so it doesn't try to pass a NULL value to the stored procedure for insertion.

这篇关于在我的存储过程中尝试了我的级别最佳错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！