当我运行存储过程显示错误 [英] When I Run The Store Procedure Shows Error

问题描述

USE [HIMT_Testing]
GO
/****** Object:  StoredProcedure [dbo].[OH_Course_Elg]    Script Date: 03/10/2015 15:51:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 ALTER Procedure [dbo].[OH_Course_Elg] @Studid varchar(100)
 as
 begin

 declare  @Coursename varchar(100),
          @code varchar(100),
          @certificate varchar(100),
          @issueauth varchar(100),
          @issuedate varchar(100),@Cousrseelg Varchar(100)
           
 create table #Temptable (Coursename varchar(100))

 create table #Temptable1 (code varchar(100),
                          certificate varchar(100),issuseauth varchar(100),issuedate varchar(100))
 begin tran


 declare coursename cursor for
   select distinct Course = case cr.cpm_pkg_id WHEN '' THEN cr.cmn_minor_code else  cbm.cmn_minor_code end 
   from course_registration cr, batch_course_registration bcr, co_batch_master cbm where bcr.cr_bill_no 
   = cr.cr_bill_no and cbm.cbm_batch_id = bcr.bcr_batch_id and cr.cr_active = 'A' --and cbm.cbm_batch_start_dt > getdate()
   and datepart(year,cbm.cbm_batch_start_dt) <> '3000' and datepart(year,cbm.cbm_batch_start_dt) <> '4000' 
   and datepart(year,cbm.cbm_batch_start_dt) <> '5000' and datepart(year,cbm.cbm_batch_start_dt) <> '6000' 
   and cr.stud_id = @Studid

 open coursename
  fetch next from coursename into @Coursename
  while @@Fetch_status = 0
     begin   
       begin tran
         declare crselg cursor for
            select distinct a.pm_prof_code as Code,a.sp_cert_no as Certificate_No,a.sp_issu_authority as Issue_Authority, convert(char(14),a.sp_issu_dt,106) as Issue_Date,
             b.Courseelg as Cousrseelg from student_professional a,tb_courseelg_settings b where a.pm_prof_code= b.courseelg and b.coursename = @Coursename
 and a.stud_id = @studid order by Issue_Date desc 

--print 'inside'
         open crselg 
            fetch next from crselg into @code,@certificate,@issueauth,@issuedate,@Cousrseelg
             while @@Fetch_status = 0
			 begin    
                 insert into #Temptable values(@Coursename)
                 insert into #Temptable1 values (@code,@certificate,@issueauth,@issuedate)
             fetch next from crselg into @code,@certificate,@issueauth,@issuedate,@Cousrseelg
             end
        close crselg
        deallocate crselg
        commit tran
    fetch next from coursename into @Coursename
    end
 close coursename
 deallocate coursename
 commit tran
select * from #Temptable
end
select * from #Temptable1
end

当我运行上面的商店程序时显示错误如下

'end'附近的语法不正确。



我的问题是什么商店程序。



问候，

Narasiman P.



添加了代码块 - OriginalGriff [/ edit]

When i run the above store procedure shows error as follows
Incorrect syntax near 'end'.

What is the problem in my store procedure.

Regards,
Narasiman P.

[edit]Code block added - OriginalGriff[/edit]

推荐答案

检查以下代码一次。

Hi,

Check this below code once.

Create Procedure [dbo].[OH_Course_Elg] @Studid varchar(100)
 as
 begin 
 declare  @Coursename varchar(100),
          @code varchar(100),
          @certificate varchar(100),
          @issueauth varchar(100),
          @issuedate varchar(100),@Cousrseelg Varchar(100)
           
 create table #Temptable (Coursename varchar(100))
 
 create table #Temptable1 (code varchar(100),
                          certificate varchar(100),issuseauth varchar(100),issuedate varchar(100))
 begin tran
 declare coursename cursor for
   select distinct Course = case cr.cpm_pkg_id WHEN '' THEN cr.cmn_minor_code else  cbm.cmn_minor_code end 
   from course_registration cr, batch_course_registration bcr, co_batch_master cbm where bcr.cr_bill_no 
   = cr.cr_bill_no and cbm.cbm_batch_id = bcr.bcr_batch_id and cr.cr_active = 'A' --and cbm.cbm_batch_start_dt > getdate()
   and datepart(year,cbm.cbm_batch_start_dt) <> '3000' and datepart(year,cbm.cbm_batch_start_dt) <> '4000' 
   and datepart(year,cbm.cbm_batch_start_dt) <> '5000' and datepart(year,cbm.cbm_batch_start_dt) <> '6000' 
   and cr.stud_id = @Studid 
 open coursename
  fetch next from coursename into @Coursename
  while @@Fetch_status = 0
     begin   
       begin tran
         declare crselg cursor for
            select distinct a.pm_prof_code as Code,a.sp_cert_no as Certificate_No,a.sp_issu_authority as Issue_Authority, convert(char(14),a.sp_issu_dt,106) as Issue_Date,
             b.Courseelg as Cousrseelg from student_professional a,tb_courseelg_settings b where a.pm_prof_code= b.courseelg and b.coursename = @Coursename
 and a.stud_id = @studid order by Issue_Date desc 
 
--print 'inside'
         open crselg 
            fetch next from crselg into @code,@certificate,@issueauth,@issuedate,@Cousrseelg
             while @@Fetch_status = 0
			 begin    
                 insert into #Temptable values(@Coursename)
                 insert into #Temptable1 values (@code,@certificate,@issueauth,@issuedate)
             fetch next from crselg into @code,@certificate,@issueauth,@issuedate,@Cousrseelg
             end
        close crselg
        deallocate crselg
        commit tran
    fetch next from coursename into @Coursename
    end
 close coursename
 deallocate coursename
 commit tran
select * from #Temptable
select * from #Temptable1
end

您的陈述：

Your statements:

select * from #Temptable
end (at here the begin will be closed so that's why it's giving error for next end).
select * from #Temptable1
end

你好，



这里是精炼的存储过程，没有错误..



USE [HIMT_Testing]

GO

/ ******对象：StoredProcedure [ dbo]。[OH_Course_Elg]脚本日期：03/10/2015 15:51:29 ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



创建程序[dbo]。[OH_Course_Elg] @Studid varchar（100）
as

开始



声明@Coursename varchar（100），

@code varchar （100），

@certificate varchar（100），

@issueauth varchar（100），

@issuedate varchar（100），@ Cousrseelg Varchar（100）



创建表#Temptable（Coursename va rchar（100））



创建表#Temptable1（代码varchar（100），

证书varchar（100），issuseauth varchar（100） ），发出varchar（100））

开始转发





声明coursename光标为

选择不同的Course = case cr.cpm_pkg_id WHEN''那么cr.cmn_minor_code else cbm.cmn_minor_code end

来自course_registration cr，batch_course_registration bcr，co_batch_master cbm其中bcr.cr_bill_no

= cr.cr_bill_no和cbm.cbm_batch_id = bcr.bcr_batch_id和cr.cr_active ='A' - 和cbm.cbm_batch_start_dt> getdate（）

和datepart（年，cbm.cbm_batch_start_dt）<> '3000'和datepart（年，cbm.cbm_batch_start_dt）<> '4000'

和datepart（年，cbm.cbm_batch_start_dt）<> '5000'和datepart（年，cbm.cbm_batch_start_dt）<> '6000'

和cr.stud_id = @Studid



打开coursename

从coursename到@取Coursename

而@@ Fetch_status = 0

开始

begin tran

声明crselg光标为

选择不同的a.pm_prof_code作为Code，a.sp_cert_no作为Certificate_No，a.sp_issu_authority作为Issue_Authority，将（char（14），a.sp_issu_dt，106）转换为Issue_Date，

b。 Courseelg作为Cousrseelg来自student_professional a，tb_courseelg_settings b其中a.pm_prof_code = b.courseelg和b.coursename = @Coursename

和a.stud_id = @studid order by Issue_Date desc



- 打印'里面'

打开crselg

从crselg获取下一个@ code，@ certificate，@ issueauth，@ issuedate， @Cousrseelg

而@@ Fetch_status = 0

开始

插入#Temptable值（@Coursename）

插入＃Temptable1值（@ code，@ certificate，@ issueauth，@ issuedate）

从crselg获取下一个@ code，@ certificate，@ issueauth，@ issateate，@ Cousrseelg

end

close crselg

deallocate crselg

commit tran

从coursename获取下一个到@Coursename

结束

关闭coursename

deallocate coursename

commit tran



结束



select * from #Temptable



select * from＃Temptable1



-----------问题在于这些陈述：



select * from #Temptable

end

select * from＃Temptable1

结束



如果你想在select语句后加上'end'，你必须再次为'End'写'Begin'声明即



开始

从#Temptable中选择*

结束



开始

从＃Temptable1中选择*

结束

Hello,

Here is refined stored procedure with no error..

USE [HIMT_Testing]
GO
/****** Object: StoredProcedure [dbo].[OH_Course_Elg] Script Date: 03/10/2015 15:51:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create Procedure [dbo].[OH_Course_Elg] @Studid varchar(100)
as
begin

declare @Coursename varchar(100),
@code varchar(100),
@certificate varchar(100),
@issueauth varchar(100),
@issuedate varchar(100),@Cousrseelg Varchar(100)

create table #Temptable (Coursename varchar(100))

create table #Temptable1 (code varchar(100),
certificate varchar(100),issuseauth varchar(100),issuedate varchar(100))
begin tran


declare coursename cursor for
select distinct Course = case cr.cpm_pkg_id WHEN '' THEN cr.cmn_minor_code else cbm.cmn_minor_code end
from course_registration cr, batch_course_registration bcr, co_batch_master cbm where bcr.cr_bill_no
= cr.cr_bill_no and cbm.cbm_batch_id = bcr.bcr_batch_id and cr.cr_active = 'A' --and cbm.cbm_batch_start_dt > getdate()
and datepart(year,cbm.cbm_batch_start_dt) <> '3000' and datepart(year,cbm.cbm_batch_start_dt) <> '4000'
and datepart(year,cbm.cbm_batch_start_dt) <> '5000' and datepart(year,cbm.cbm_batch_start_dt) <> '6000'
and cr.stud_id = @Studid

open coursename
fetch next from coursename into @Coursename
while @@Fetch_status = 0
begin
begin tran
declare crselg cursor for
select distinct a.pm_prof_code as Code,a.sp_cert_no as Certificate_No,a.sp_issu_authority as Issue_Authority, convert(char(14),a.sp_issu_dt,106) as Issue_Date,
b.Courseelg as Cousrseelg from student_professional a,tb_courseelg_settings b where a.pm_prof_code= b.courseelg and b.coursename = @Coursename
and a.stud_id = @studid order by Issue_Date desc

--print 'inside'
open crselg
fetch next from crselg into @code,@certificate,@issueauth,@issuedate,@Cousrseelg
while @@Fetch_status = 0
begin
insert into #Temptable values(@Coursename)
insert into #Temptable1 values (@code,@certificate,@issueauth,@issuedate)
fetch next from crselg into @code,@certificate,@issueauth,@issuedate,@Cousrseelg
end
close crselg
deallocate crselg
commit tran
fetch next from coursename into @Coursename
End
close coursename
deallocate coursename
commit tran

End

select * from #Temptable

select * from #Temptable1

-----------the problem was with this statements:

select * from #Temptable
end
select * from #Temptable1
end

If you want to put 'end' after select statement you have to again write 'Begin' For 'End' statement i.e

Begin
select * from #Temptable
end

Begin
select * from #Temptable1
end

这篇关于当我运行存储过程显示错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！