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 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?
Jitendra KumarPosted Dec 26, 2014, 3:07 AM
narasiman raoPosted Dec 26, 2014, 2:55 AM
Tb_course_keyword Table Structure as follows (Tb_course_keyword )
SNO Keyword Coursecode Active
1 AFF AFF A
Eligibility Structure as follows (Eligibility)
Minorcode Eligbility
EFC REO
I provide the table structure for both the table.
Kindly please help me.
Jitendra KumarPosted Dec 26, 2014, 2:07 AM
kindly provides the tables data that tables you used in the stored proc.
narasiman raoPosted Dec 26, 2014, 12:59 AM
exec [Shor_code] 'EFC','elg'
Eligibilty for EFC : - REO
But when i execute my above stored procedure output i get as follows
exec [Short_Code] 'efc','elg'
NUll (this is wrong output)
Please help me from my above stored procedure what is the mistake i made?
Jitendra KumarPosted Dec 26, 2014, 12:00 AM
What output you want??