TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
narasiman rao
NA
519
771.6k
The above stored procedure output is not coming correctly
Dec 25 2014 9:50 PM
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?
Reply
Answers (
5
)
How to Access Records
Dynamic select query