what is the error in the stored procedure

Question

0.00/5 (No votes)

See more:

Hi friends

This is my stored procedure

SQL

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

CSS

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

CSS

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

studentAdmission table

CSS

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_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

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

Advance thanks

Posted 1-Jan-13 19:08pm

baskaran chellasamy

Updated 1-Jan-13 20:29pm

v2

Add a Solution

Comments

prashant patil 4987 2-Jan-13 2:45am

hey add out parameter for returning value.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

P.S Vijay · Answer 1 · 2013-01-01T19:43:00

Hi Baskaran

Try with this

SQL

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

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