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.
Amit Kumar SinghPosted Mar 1, 2016, 5:28 AM
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
)
/* Create Procedure */
CREATE PROCEDURE [dbo].[CourseRankWiseSearch]
(
@Rank VARCHAR(20),
@FromDate VARCHAR(20),
@ToDate VARCHAR(20),
@Type VARCHAR(20)
)
AS
BEGIN
DECLARE @Course VARCHAR(100),@Code VARCHAR(100),@Description VARCHAR(max),@Eligbility VARCHAR(max),@Days VARCHAR(20),@Startdt datetime,@Enddt datetime
IF(@Type='Insert')
BEGIN
INSERT INTO #TestTable5
(
Course
,Code
,Description
,Days
,Startdt
,Enddt
)
VALUES
(
@Course
,@Code
,@Description
,@Days
,@Startdt
,@Enddt
)
END
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
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
/* Execute the procedure */
---CourseRankWiseSearch 'SSO','2012-01-01','2012-12-30','Crs'