stored procedure problem

Posted by Lakn2 under Sql Server on 6/25/2011 | Points: 10 | Views : 1544 | Status : [Member] | Replies : 16
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.



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response