Doubt regarding query and its exceution

Question

0.00/5 (No votes)

See more:

hi all,

in my database i am using one stored procedure for getting all the leaves of the employees.

when i am right click on that stored procedure---> Excecute procedure----->giving the required values---->then ok

it is exceuting and showing as 1 row(s) affected but that particular row is not showing in output.

can anyone please help me on this issue and what may be the problem why this occurs?

my storedprocedure it is very big please don't mind

ALTER PROCEDURE [dbo].[SPGetEmployeeLeavesReportforall] 
	-- Add the parameters for the stored procedure here
	@EmployeeId varchar(50),
	@StartDate datetime,
	@EndDate datetime,
	@LeaveType int,
	 @SupervisorId varchar(20),
	 @StatusId int
	AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    -- Insert statements for procedure here
    
  if(@EmployeeId = 'All')
  begin  
		if(@LeaveType = 0)
		begin
		if(@StartDate = '01/01/1753' and @EndDate = '01/01/1753')
					begin
					     if(@StatusId = 0)
							begin		
						select l.AppliedDate,l.ApprovedDate,l.ReasonForLEave,e.FullName,a.Status ,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
						(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy
						 from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where  l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId 
and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
					end
				else
					begin
					   select l.AppliedDate,l.ApprovedDate,l.ReasonForLEave,e.FullName,a.Status ,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
						(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy
						 from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where  l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.Status = @StatusId 
and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
					
					end
					
		 		end
				else 
				begin
					if(@StatusId = 0)
					begin
					select e.FullName,a.Status ,l.ReasonForLEave,l.AppliedDate,l.ApprovedDate,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy
					 from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.FromDate >=@StartDate and l.ToDate <=@EndDate
 and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId 
 and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
				end
				else
					begin
						 select e.FullName,a.Status ,l.ReasonForLEave,l.AppliedDate,l.ApprovedDate,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy
					 from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.FromDate >=@StartDate and l.ToDate <=@EndDate
 and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.Status = @StatusId
 and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc 
					
					end	
				
				end
		end		
		else
			begin
			   if(@StartDate = '01/01/1753' and @EndDate = '01/01/1753')
					begin
						if(@StatusId = 0)
						begin	
						select e.FullName,l.AppliedDate,l.ReasonForLEave,l.ApprovedDate,a.Status ,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where  l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType   and 
l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
					end
					else
						begin
							 select e.FullName,l.AppliedDate,l.ReasonForLEave,l.ApprovedDate,a.Status ,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where  l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType  and l.Status = @StatusId  and 
l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
						end
		 		end
				else 
				begin
					if(@StatusId = 0)
					begin
					select e.FullName,a.Status ,l.AppliedDate,l.ReasonForLEave,l.ApprovedDate,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType
and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
				end
				else
					begin
							select e.FullName,a.Status ,l.AppliedDate,l.ReasonForLEave,l.ApprovedDate,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType
and l.Status = @StatusId and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
					end
				end
		   end
   end
   
   else	
		begin
		if(@LeaveType = 0)
			begin		
			if(@StartDate = '01/01/1753' and @EndDate = '01/01/1753')
					begin
						if(@StatusId = 0)
						begin	
						select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.AppliedDate,l.ApprovedDate,l.ToDate,l.NoofHrs,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.EmployeeID = @EmployeeId  and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId order by FromDate desc
		 		end
		 		else
		 			begin
		 					select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.AppliedDate,l.ApprovedDate,l.ToDate,l.NoofHrs,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.EmployeeID = @EmployeeId  and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.Status = @StatusId order by FromDate desc
		 			end
		 			end
				else 
				begin
					if(@StatusId = 0)
					begin
					select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.EmployeeID = @EmployeeId and l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId 
order by FromDate desc
				end
				else
					begin
					   select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.EmployeeID = @EmployeeId and l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.Status=@StatusId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId 
order by FromDate desc
					end
					
				end
			end
		else
			begin
			  if(@StartDate = '01/01/1753' and @EndDate = '01/01/1753')
					begin
						if(@StatusId = 0)
						begin
						select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.EmployeeID = @EmployeeId  and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType
order by FromDate desc
                     end
                 else
					begin
					   select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.EmployeeID = @EmployeeId and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.Status=@StatusId and l.LeaveTypeId = @LeaveType
order by FromDate desc
					end
                     
		 		end
				else 
				begin
					if(@StatusId = 0)
					begin
					select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.EmployeeID = @EmployeeId and l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId   and l.LeaveTypeId = @LeaveType
order by FromDate desc
                   end
                   else
						begin
							select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
					(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a 
where l.EmployeeID = @EmployeeId and l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.Status=@StatusId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId   and l.LeaveTypeId = @LeaveType
order by FromDate desc
						
						end
                   
				end
			end	
		end
    
 END

Posted 28-Dec-11 0:47am

mandarapu

Updated 28-Dec-11 1:05am

v2

Add a Solution

Comments

nagendrathecoder 28-Dec-11 6:53am

Can you show us your SP?

anushripatil 28-Dec-11 6:59am

can u show exec statement you are trying. & the select statement that u r checking to c ur answer

anushripatil 28-Dec-11 7:10am

just to track that what is getting executed u can write PRINT '1', Print '2'... in each of ur Begin... end block & then u can track that which statement is getting executed & then try printing that . Hope u will get ur solution

4 solutions

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)

palraj001 · Answer 1 · 2011-12-28T00:50:00

Solution 1

hi,

I think there is no select statement. Sp may update or delete or insert row.

You might be used Temp tables for some operations. it may give this row affected msg.

check your Sp

regards,
Pal

Posted 28-Dec-11 0:50am

palraj001

Updated 28-Dec-11 0:51am

v2

Comments

palraj001 28-Dec-11 7:12am

use print 'check' or select 'check' in each condition block. thus you can check whether the block of code executed or not

Wendelius · Answer 2 · 2011-12-28T00:52:00

Solution 2

If your stored procedure updates the data in the database you'll get info how many rows are updated. No output for the data is generated unless the procedure also returns rows (a select statement is coded in the procedure).

If you mean that when you execute the procedure from the calling side, then you should get the amount of affected rows then take the return value from ExecuteNonQuery and check that.

Posted 28-Dec-11 0:52am

Wendelius

Comments

mandarapu 28-Dec-11 6:56am

hi,
that particular record is present in database but when iam exceuting it is not showing.

Wendelius 28-Dec-11 9:23am

Since your procedure is very dependent on the parameters, it's impossible to say if any of those select statements actually gets executed.

One easy way is to use PRINT statement in the beginning of the procedure to print out the values that are received by the stored procedure. This way you can verify that correct values are used and check what portion of the logic is executed. If needed add more print statements to track the execution.

Sarvesh Kumar Gupta · Answer 3 · 2011-12-28T00:54:00

Solution 3

You have not used select query, there is problem in sp, so show your sp from which i can help for providing a proper solution.

Posted 28-Dec-11 0:54am

Sarvesh Kumar Gupta

Comments

mandarapu 28-Dec-11 7:02am

i am using select statements also for some employees it is displaying for some i am facing error

uspatel · Answer 4 · 2011-12-28T00:55:00

Solution 4

right click in your query editor and
goto results to
->Results to grid
then execute query
hope it works.

Posted 28-Dec-11 0:55am

uspatel