存储过程中的错误是什么

问题描述

嗨朋友



这是我的存储过程

Hi friends

This is my stored procedure

alter procedure sp_getstudentforsearchbyrollno(@rollno int)
as
begin
select sar.AdmissionNumber,ra.RollNumber,sa.StudentName,cn.Standered_name,sn.Section_name from School.dbo.StudentRollNumberAllocation_details ra  join  School.dbo.StudentAdmission_details  sa on sa.AdmissionNumber=ra.AdmissionNumber  join School.dbo.Student_Acadamic_details  sar on sar.AdmissionNumber=ra.AdmissionNumber  join School.dbo.Standered_details cn on cn.Standered_id=ra.classid  join School.dbo.Section_details sn on sn.Section_id=sar.Section_id where ra.RollId=@rollno
end

StudentRollNumberallocationtable

StudentRollNumberallocationtable

RollId  RollNumber  AdmissionNumber AcdemicYearId   Createddatetime Updateddatetime adminid classid
5   12130100    103 11  2012-11-25 12:35:11.060 NULL    5002    12
6   12130101    102 11  2012-11-25 12:35:11.073 NULL    5002    12
7   12130102    100 11  2012-11-25 14:02:33.327 NULL    5002    12
8   12130103    101 11  2012-11-25 14:02:33.357 NULL    5002    12

Student_Acadamic_details

Student_Acadamic_details

AdmissionNumber Section_id  Acadamic_year_id    Created_date_time   updateddatetime adminid classid
100 51  11  2012-11-29 16:35:44.653 NULL    5002    12
101 52  11  2012-11-29 16:35:44.653 2012-11-30 12:56:59.160 5002    12
102 51  11  2012-11-29 16:35:44.653 NULL    5002    12
103 51  11  2012-11-29 16:35:44.653 NULL    5002    12

stu dentAdmission表

studentAdmission table

AdmissionNumber ApplicationNumber   Admissiondate   Academic_year_id    ClassId StudentName DOB Gender  Age
100 1000    2012-10-31 10:59:49.000 11  12  Chandru 2007-10-30  M   5
101 1001    2012-11-02 13:15:30.000 11  12  kandee  2007-11-02  M   5
102 1002    2012-11-05 11:19:18.000 11  12  Gayathiri   2005-11-05  F   7
103 1003    2012-11-05 11:19:18.000 11  12  divya   2006-11-05  F   6

节表

section table

Section_id	Section_name	Standered_id	Acadamic_year_id
51	A	12	11
52	B	12	11
101	A	12	12
102	B	12	12

standered table

standered table

Standered_id	Standered_name	Branch_id	Acadamic_year_id
12	FirstClass	141	11
13	Second Class	141	11
14	Third clas	141	11
15	Fourth Class	141	11
16	Fifth Class	141	11
50	Fisrt Class	141	12

存储过程不会返回任何后续查询的值

stored procedure doesnot return any value for following query

exec sp_getstudentforsearchbyrollno 12130101

有什么问题。如何通过解决方案跟踪内部联接中的值（f11）​​



预先感谢

what is the problem. how can i trace value in inner join by trace by solution(f11)

Advance thanks

推荐答案

ALTER procedure sp_getstudentforsearchbyrollno(@rollno int)
as
begin

DECLARE @tab table (AdmissionNumber int, RollNumber int, StudentName nvarchar(500),Standered_name nvarchar(500),Section_name nvarchar(500))

INSERT @tab
select sar.AdmissionNumber,ra.RollNumber,sa.StudentName,cn.Standered_name,sn.Section_name from School.dbo.StudentRollNumberAllocation_details ra  join  School.dbo.StudentAdmission_details  sa on sa.AdmissionNumber=ra.AdmissionNumber  join School.dbo.Student_Acadamic_details  sar on sar.AdmissionNumber=ra.AdmissionNumber  join School.dbo.Standered_details cn on cn.Standered_id=ra.classid  join School.dbo.Section_details sn on sn.Section_id=sar.Section_id where ra.RollId=@rollno

SELECT * FROM @tab

end

另一种方法是从用户定义的函数返回一个表变量，我不是这个方法的忠实粉丝。但它适合您的要求。



问候，

Vijay

An alternative approach is to return a table variable from a user defined function , I am not a big fan of this method. But it is fit for your requirement.

Regards,
Vijay

这篇关于存储过程中的错误是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！