narasiman rao

narasiman rao

  • NA
  • 519
  • 768.8k

Error in my stored procedure

Mar 1 2016 1:07 AM
 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 values(Course,Code,Description,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,c.cmn_minor_Desc as eligbility,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
where b.cmn_minor_code LIKE '%SSO%' and b.cbm_active <> 'd'
and b.cmn_minor_code = c.cmn_minor_code
and b.cbm_batch_start_dt between '2012-01-01 00:00:00.000' and '2012-12-30 00:00:00.000'

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
 
 
 When i run the above stored procedure error as follows
 
The name "Course" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
The above error shows in below line as follows
 
insert into #TestTable5 values(Course,Code,Description,Days,Startdt,Enddt)
 
please help me what is error in my above stored procedure.
 
 
 
 

Answers (1)