Get more than one count value from different tables in single stored procedure

Posted by Gopal_nivas under Sql Server on 6/20/2011 | Points: 10 | Views : 1957 | Status : [Member] | Replies : 2
hi..

i want to display count value from different table based on date.

ie)
select count(*) from a where fromdate=@frmdate
select count(*) from b where fromdate=@frmdate

here i was using two different proc. to get the out put.

now i want to get the count value from different table and it will return 2 different count values.

in code behind also i want to get 2 count values and i have to use it for different purposes.

so how to get this in a single stored procedure.
need ur suggetsions with examples...




Responses

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

Up
0
Down
hi..
Try this Code


Create procedure tes

As
Begin
declare @s1 int
declare @s2 int
select @s1=count(*) from emp
select @s2=count(*) from emp1
select @s1,@s2
End


http://vbdotnetaddict.blogspot.com/

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

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

Up
0
Down
Hi

You can use OUTPUT parameter...
Create Proc USP_Procedure1

(
@Counta BigInt Output,
@Countb BigInt Output
)As
Begin
Set Nocount on
Declare @a BigInt,
@b BigInt
select @a = count(1) from a where fromdate=@frmdate
select @b = count(1) from b where fromdate=@frmdate
Select @Counta = @a, @Countb = @b
End
Go
Declare @a BigInt,

@b BigInt
Exec USP_Procedure1 @a Output, @b Output
Select @a , @b
Go

Note: You can also use sys.sysindexes DMV to get the recordcount for a particular table...
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Login to post response