I Tried But My Stored Procedure I Am Not Able To Get Correct Output

See more:

C#5.0

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[Short_Code](@Keyword varchar(10),@course varchar(10))
as
begin

declare @Batchdate varchar(20),
@AllBatchdate varchar(20),
@Coursefees varchar(50),
@Coursefees1 numeric(20),
@coursecode varchar(20),
@CHK int,
@MSG varchar(max),
@final varchar(max),
@courseelg varchar(max),
@MajorCode varchar(20)

SET @CHK=0
SET @MSG= ''
set @Batchdate = ''
set @Coursefees = '0'

SET @Keyword = UPPER(@Keyword)
select @CHK=COUNT(*),@coursecode = b.course_code from Tb_Course_Keyword as b where b.Keyword = @Keyword and b.Active <>'d' group by b.course_code

if @CHK !=0
begin
if @course = crs
begin
declare coursedate cursor for

select TOP 3 Batch_Date = convert(char(6),a.cbm_batch_start_dt,113), a.cmj_major_code from CO_BATCH_MASTER a, co_batch_number b where (b.cbn_batch_nos-(SELECT
COUNT(*) FROM BATCH_COURSE_REGISTRATION BCR,COURSE_REGISTRATION CR WHERE BCR.cr_bill_no = CR.cr_bill_no and BCR.bcr_batch_id = b.cbm_batch_id AND CR.cr_active a.cmn_minor_code= @coursecode and a.cbm_batch_id=b.cbm_batch_id and a.cbm_batch_start_dt >= getdate() and b.cbn_batch_no=1 and
<(b.cbn_batch_nos-((select><> 'D') + (isnull((select top 1 noofsheets from bulkbooking where + (isnull((select top 1 noofsheets from bulkbooking where batchid=a.cbm_batch_id and active<>'D'),0)))) order by a.cbm_batch_start_dt

set @AllBatchdate =''
open coursedate

fetch next from coursedate into @Batchdate, @MajorCode
while @@Fetch_status = 0
begin
set @AllBatchdate = @AllBatchdate + @Batchdate + ','

select top 1 @Coursefees = isnull(crm_course_rate,0) from co_rate_master where cmn_minor_code = @coursecode and crm_active <>'d'


fetch next from coursedate into @Batchdate, @MajorCode
end
close coursedate
deallocate coursedate
end
else if @course = 'elg'
begin
select distinct top 1 @courseelg = Eligibility from Eligibility where minorcode = @coursecode
set @final = 'Eligibilty for '+ @coursecode +':' + @courseelg
set @MSG = ''
goto ex;
end
else if @chk =0
begin
set @course = 'elg'
select distinct top 1 @courseelg = (select Eligibility from Eligibility where minorcode = @Keyword)

set @final = 'Eligibilty for '+ 'EFC' +' : ' + @courseelg
end
end

if @MSG = ''
begin
if @AllBatchdate = ''
begin
set @final = 'Dear Student, Thanks for contacting us'for '+ @Keyword + ' Batch dates'
end
else
begin

if @coursecode = 'DNS'
set @final = @Keyword + ' fees is Rs ' + @Coursefees + '. Visit website'
else
set @final = 'Next avaliability for ' + @Keyword + ' - ' + @AllBatchdate + ' and the fees is Rs ' + @Coursefees + '. visit website'

end
end
else
begin

set @final = 'Invalid Keyword. Sorry try again with valid keyword'
end
Ex:
select @final
end

Select * from Tb_course_keyword

SN0 Keyword courscode active
1 AFF AFF A
2 APS APS A

select * from Eligibility 

Minorcode Eligbility
EFC REO

i want to validate if Tb_course_keyword EFC Keyword is not there
and in Eligibility table EFC(minor code is there)

When i execute the store procedure i want output as follows

exec [Shor_code] 'EFC','elg'

Eligibilty for EFC : - REO

For getting a output
Eligibilty for EFC : - REO

Code as follows

if @chk =0
begin
set @course = 'elg';
select distinct top 1 @courseelg = (select Eligibility from Eligibility where minorcode = @Keyword)
set @final = 'Eligibilty for '+ 'EFC' +' : ' + @courseelg

from my above code what is the mistake i made.

But when i execute the output as follows

exec [Short_Code] 'efc','elg'

NUll

From my above store procedure what is the mistake i made?