选择问题的存储过程 [英] store procedure for select problem

问题描述

你好朋友.

以下是我的存储过程

Hello friends.

The below is my store procedure

create procedure sp_insertclass(@standeredid int,@standeredname varchar(10),@branchname varchar(50) ,@yearfrom smallint,@yearto smallint,@createddatetime datetime)
as
begin
declare @acadamicid int;
declare @branchid int;
select @branchid=Branch_id from Branch where Branch_name=@branchname;
select @acadamicid=Acadamic_year_id from Acadamic_year where Acadamic_year_from=@yearfrom and Acadamic_year_to=@yearto;
insert into Standered_details(Standered_id,Standered_name,Branch_id,Acadamic_year_id,Created_date_time)values(@standeredid,@standeredname,@branchid,@acadamicid,@createddatetime)
end

执行上面的查询时会成功.但是通过此查询插入日期时，会发生异常，例如Acadamic_year_id为null.但是我正确输入了每年的年份.请告诉我任务中可能有什么问题
我做的一件事是在C#类中使用int数据类型声明yearfrom和yearto变量.在这个地方是否有问题.
预先感谢

When executing the above query it succeed.but when insert date through this query the exception occur like Acadamic_year_id is null. but i entered the from year and toyear correctly. please tell me what could be problem in task
One thing i did is yearfrom and yearto variable declared with int datatypes in c# class.Is it make a problem in this place.
Thank in advance

推荐答案

检查表中是否存在给定日期和日期的任何学年.我认为表中没有记录.


从Acadamic_year中选择@ acadamicid = Acadamic_year_id，其中Acadamic_year_from = @ yearfrom和Acadamic_year_to = @ yearto;

如果存在，则将begin tran包含在存储的proc的开头，并在插入后提交tran

Check whether any academic year is present in the table for the given from date and todate. I think there are no records in the table.


select @acadamicid=Acadamic_year_id from Acadamic_year where Acadamic_year_from=@yearfrom and Acadamic_year_to=@yearto;

If its present, then include begin tran in the starting of stored proc and commit tran after insert

与解决方案1所说的没什么不同，但是要添加验证以检查是否为空值在@acadamicid参数中，尝试以下操作:

Not much different to what Solution 1 has to say, but add validation to check for a null value in the @acadamicid parameter, try this:

if(@acadamicid is not null)
begin

select @acadamicid=Acadamic_year_id from Acadamic_year where Acadamic_year_from=@yearfrom and Acadamic_year_to=@yearto;
insert into Standered_details(Standered_id,Standered_name,Branch_id,Acadamic_year_id,Created_date_time)
values(@standeredid,@standeredname,@branchid,@acadamicid,@createddatetime)
    
end

这篇关于选择问题的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！