stored procedure problem

Posted by Lakn2 under Sql Server on 6/25/2011 | Points: 10 | Views : 2444 | Status : [Member] | Replies : 16

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi i am using this sp in crystal report but it returns values of 2nd query only but i want values of 1st and 2nd query

create procedure [dbo].[get_finalcash]
(@regdno varchar(25),
@total money,
@advance money,
@deduction money,
@netamount money,
@todate datetime,
@paid money,
@due money,
@head varchar(25))
as
begin
if(@head='Room Rent')
begin
select f.regdno,f.name,f.doa,f.time,f.advance,f.dod,f.time1,f.billno,e.rddp,e.napa,e.na,e.total from finalbill f
inner join efinalbill e on f.regdno=e.regdno where f.regdno=@regdno
end
else
begin
select sum(e.total) as Total,regdno,head from efinalbill e
where e.regdno=@regdno and e.head=@head group by e.regdno,e.head
end
end

Thanks&Regards
LakshmiNarayana Nalluri.

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Vilasgite080 on: 6/25/2011 [Member] Starter | Points: 25

0	hi you are using if else statement in it. so please check its match with that case. Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: T.saravanan on: 6/25/2011 [Member] [MVP] Silver | Points: 25

0	Hi, You did not mentioned why you use the condition in your SP. If don't need the if condition.. Change your SP like... Create procedure [dbo].[get_finalcash] (@regdno varchar(25), @total money, @advance money, @deduction money, @netamount money, @todate datetime, @paid money, @due money, @head varchar(25)) as begin select f.regdno,f.name,f.doa,f.time,f.advance,f.dod,f.time1,f.billno,e.rddp,e.napa,e.na,e.total from finalbill f inner join efinalbill e on f.regdno=e.regdno where f.regdno=@regdno select sum(e.total) as Total,regdno,head from efinalbill e where e.regdno=@regdno and e.head=@head group by e.regdno,e.head end Thanks, T.Saravanan Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

You did not mentioned why you use the condition in your SP.
If don't need the if condition..
Change your SP like...

Create procedure [dbo].[get_finalcash]

(@regdno varchar(25),

@total money,

@advance money,

@deduction money,

@netamount money,

@todate datetime,

@paid money,

@due money,

@head varchar(25))

as

begin  

           select f.regdno,f.name,f.doa,f.time,f.advance,f.dod,f.time1,f.billno,e.rddp,e.napa,e.na,e.total from finalbill f

           inner join efinalbill e on f.regdno=e.regdno where f.regdno=@regdno

     

           select sum(e.total) as Total,regdno,head from efinalbill e

           where e.regdno=@regdno and e.head=@head group by e.regdno,e.head

end

Thanks,
T.Saravanan

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 6/27/2011 [Member] Starter | Points: 25

0	hi saravanan i need condition because i am using this in crystalreport and my requirement is,i have one dropdownlist it contains 4 values 1.roomrent 2.surgeon fee 3. consultant fee 3.others if i select roomrent then it returns first query values if i select 2,3,4 it returns i want 1st query values and second query values but i am getting 2nd query values only if i put if else otherwise it display 1st queryvalues only from your sp also it does n't return second query value. Thanks&Regards LakshmiNarayana Nalluri. Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

hi saravanan i need condition because i am using this in crystalreport and my requirement is,i have one dropdownlist it contains 4 values

1.roomrent
2.surgeon fee
3. consultant fee
3.others

if i select roomrent then it returns first query values
if i select 2,3,4 it returns i want 1st query values and second query values
but i am getting 2nd query values only if i put if else otherwise it display 1st queryvalues only from your sp also it does n't return second query value.

Thanks&Regards
LakshmiNarayana Nalluri.

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bugwee on: 6/27/2011 [Member] Starter | Points: 25

0	hi, check the parameter value you are passing... let us say, Room Rent is different from roomrent Regards, Bugwee Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Lakn2 on: 6/27/2011 [Member] Starter | Points: 25

0	that is not problem at all Thanks&Regards LakshmiNarayana Nalluri. Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bugwee on: 6/27/2011 [Member] Starter | Points: 25

0	hi, try to use this SP IF OBJECT_ID('get_finalcash') IS NOT NULL DROP PROCEDURE get_finalcash GO create procedure [dbo].[get_finalcash] (@regdno varchar(25) = Null, @total money = Null, @advance money = Null, @deduction money = Null, @netamount money = Null, @todate datetime = Null, @paid money = Null, @due money = Null, @head varchar(25) = Null) as if @head='Room Rent' begin select f.regdno,f.name,f.doa,f.time,f.advance,f.dod,f.time1,f.billno,e.rddp,e.napa,e.na,e.total from finalbill f inner join efinalbill e on f.regdno=e.regdno where f.regdno=@regdno end else select sum(e.total) as Total,regdno,head from efinalbill e where e.regdno=@regdno and e.head=@head group by e.regdno,e.head GO Regards, Bugwee Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

hi,

try to use this SP

IF OBJECT_ID('get_finalcash') IS NOT NULL

DROP PROCEDURE get_finalcash

GO



create procedure [dbo].[get_finalcash]

(@regdno varchar(25) = Null,

@total money = Null,

@advance money = Null,

@deduction money = Null,

@netamount money = Null,

@todate datetime = Null,

@paid money = Null,

@due money = Null,

@head varchar(25) = Null)

as



if @head='Room Rent'

	begin

		select f.regdno,f.name,f.doa,f.time,f.advance,f.dod,f.time1,f.billno,e.rddp,e.napa,e.na,e.total from finalbill f

		inner join efinalbill e on f.regdno=e.regdno where f.regdno=@regdno

	end

else

select sum(e.total) as Total,regdno,head from efinalbill e where e.regdno=@regdno and e.head=@head group by e.regdno,e.head

GO

Regards,
Bugwee

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 6/27/2011 [Member] Starter | Points: 25

0	i am getting 2nd statement values only in CR when i am connecting sp from cr that is my problem Thanks&Regards LakshmiNarayana Nalluri. Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: PandianS on: 6/27/2011 [Member] [MVP] Silver | Points: 25

0	Hi - Can you please give some records from finalbill, efinalbill tables ? - Try to combine these two(If and Else) statements into a single, so you will get single result set with full information. But, you can not use Union/Union All like set operator as these two statements differes the column structure. So, you have to JOIN first result set with Second one and combine the result into single. Cheers www.sqlserverbuddy.blogspot.com Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

- Can you please give some records from finalbill, efinalbill tables ?
- Try to combine these two(If and Else) statements into a single, so you will get single result set with full information. But, you can not use Union/Union All like set operator as these two statements differes the column structure. So, you have to JOIN first result set with Second one and combine the result into single.

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bugwee on: 6/27/2011 [Member] Starter | Points: 25

0	how did you connect with CR? did you just preview the report using CR? or you preview the report programmatically using VStudio? Regards, Bugwee Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bugwee on: 6/27/2011 [Member] Starter | Points: 25

0	hi, i have attached report sample using crystal report 8.5 I am using a stored procedure with if statement and this is working fine. this is the SP using northwind database use[Northwind] IF OBJECT_ID('spWithConditionForCR') IS NOT NULL DROP PROCEDURE spWithConditionForCR GO CREATE PROCEDURE spWithConditionForCR @Title as varchar(30) = Null, @LastName as varchar(20) = Null AS if @LastName = 'King' begin Select LastName,FirstName,Title from Employees where Title=@Title and LastName=@LastName end else begin Select LastName,FirstName,Title from Employees where Title=@Title end GO Regards, Bugwee Download source file Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

hi,

i have attached report sample using crystal report 8.5
I am using a stored procedure with if statement and this is working fine.

this is the SP using northwind database

use[Northwind]



IF OBJECT_ID('spWithConditionForCR') IS NOT NULL

DROP PROCEDURE spWithConditionForCR

GO



CREATE  PROCEDURE spWithConditionForCR

	@Title as varchar(30) = Null,

	@LastName as varchar(20) = Null

AS



if @LastName = 'King'

begin

	Select LastName,FirstName,Title from Employees where Title=@Title and LastName=@LastName

end

else

begin

	Select LastName,FirstName,Title from Employees where Title=@Title

end

GO

Regards,
Bugwee

Download source file

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 6/27/2011 [Member] Starter | Points: 25

0	i am connecting sp to cr as add crystalreport to vs -> go to database expert -> select create new connection -> select oledb ->select microsoft provider for oledb sql server -> provide credentilas like server uid pwd database -> then you can find tables and sp's select sp getfinalbill now you can find 2nd selecet stmt result only. that is my problem i want all column values of 1st statement and and 2nd statement values also. Thanks&Regards LakshmiNarayana Nalluri. Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

i am connecting sp to cr as

add crystalreport to vs -> go to database expert -> select create new connection -> select oledb ->select microsoft provider for oledb sql server -> provide credentilas like server uid pwd database -> then you can find tables and sp's select sp getfinalbill now you can find 2nd selecet stmt result only. that is my problem i want all column values of 1st statement and and 2nd statement values also.

Thanks&Regards
LakshmiNarayana Nalluri.

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bugwee on: 6/27/2011 [Member] Starter | Points: 25

0	alright. im sorry. i now got what you mean. you want to display the column values of the 1st and 2nd statement in the field explorer. and it only displays the 2nd select statement. am i right? The simple solution there, i think you need to create 2 stored procedures in a single CR or you need to create another CR for each condition. Regards, Bugwee Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

alright. im sorry. i now got what you mean.

you want to display the column values of the 1st and 2nd statement in the field explorer. and it only displays the 2nd select statement. am i right?
The simple solution there, i think you need to create 2 stored procedures in a single CR or you need to create another CR for each condition.

Regards,
Bugwee

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 6/27/2011 [Member] Starter | Points: 25

0	if we place 2 sps in cr no o/p will be get. so use single sp and single cr i want my output . that is all values from 1st stmt and only totla value from 2nd stmt. Thanks&Regards LakshmiNarayana Nalluri. Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Lakn2 on: 6/28/2011 [Member] Starter | Points: 25

0	hi bugwee did you try it Thanks&Regards LakshmiNarayana Nalluri. Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bugwee on: 6/28/2011 [Member] Starter | Points: 25

0	Hi Lank2, I didn't got what u want to display.. I think in that case, there is no need to create a condition on you stored procedure. The condition should might be written on the crystal report field or you may create a formula for that. by the way what do you mean by "o/p "? if we place 2 sps in cr no o/p will be get. Regards, Bugwee Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Lank2,

I didn't got what u want to display.. I think in that case, there is no need to create a condition on you stored procedure. The condition should might be written on the crystal report field or you may create a formula for that.

by the way what do you mean by "o/p "?
if we place 2 sps in cr no o/p will be get.

Regards,
Bugwee

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 6/28/2011 [Member] Starter | Points: 25

0	o/p -> output without condition how can we get result. i had clearly mentioned that i want to display for room rent -> 1st query result for Surgeon fee , Consultant Fee, Other Expenses - > 1st query + second query result Thanks&Regards LakshmiNarayana Nalluri. Lakn2, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts