You must Sign In to post a response.
  • Category: SQL Server

    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

    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
     ELSE '' END AS 
    ,
    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
  • #764940
    In your table design you are not allowing the null values for "Course". But in your Stored procedure you are trying to insert the null values for "Course"

    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
    )

    In the above section you are just declared the values

    @Course varchar(100),
    @Code varchar(100),
    @Descr varchar(max),
    @Eligbility varchar(max),
    @Days varchar(20),
    @Startdt datetime,
    @Enddt datetime

    If you declared that variable will store "NULL". Assign the default values for declared values then try to insert the values to the table. Before insert the value set the values

    set @Course = ""
    ...
    ...

    By Nathan
    Direction is important than speed

  • #764941
    Hi

    you can alter the procedure and try them working good



    --exec [CourseRankWiseSearch] 'COURSE','CODE','DESCRIPTION',12, '2012-01-01','2012-12-30'
    exec [CourseRankWiseSearch] 'SSO', '2012-01-01', '2012-12-30', 'crs'



    ALTER proc [dbo].[CourseRankWiseSearch]
    (
    @Rank varchar(20),@FromDate varchar(20),@ToDate varchar(20),@Type varchar(20)
    )
    as
    begin

    SELECT GETDATE()

    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
    )

    SET @Course='AA'
    SET @Code='BB'
    SET @Descr='CC'
    SET @Days='12'
    SET @Startdt='2016-03-01 15:49:10.030'
    SET @Enddt='2016-03-02 15:49:10.030'

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

    --insert into #TestTable5(Course,Code,Description,Days,Startdt,Enddt)Values('AA','VV','DDD','18','2016-03-01 15:49:10.030','2016-03-02 15:49:10.030')


    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 1 ELSE '' END ,
    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

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #764947
    as error text suggest your 'Course' column does not allow NULL value and your stored procedure insert NULL in it
    first debug your stored procedure, I have write an article on it, you can check on below link
    http://www.dotnetspider.com/resources/43995-Debug-your-stored-procedure.aspx

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #765002
    Hi,

    As per error details "Cannot insert the value NULL into column 'Course', table 'tempdb.dbo.#TestTable5 00000000000F'; column does not allow nulls. INSERT fails." you can't able to insert null data into column "Course", because the column "Course" is not nullable field, either you have to pass the value while insert time or declare that column as nullable column.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments