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 : 2984 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts