ASP.NET - one store proc but return two value - Asked By msakt on 12-Jun-12 06:50 AM

select count(GroupName) from GroupCustomerManagement where BusinessID = 9
select count(GroupName) from GroupCustomerManagement where BusinessID = 9 and Active =1


this is my sp..how crate sp for both query...at the time i want both result in ui
TSN ... replied to msakt on 12-Jun-12 07:01 AM

Hi…

 

You can do this by giving multiple output parameters in the store proc

 

CREATE PROCEDURE GetImmediateManager

   @employeeID INT,

   @managerID INT OUTPUT

   @managerMobile INT OUTPUT

AS

BEGIN

   SELECT @managerID = ManagerID,@ManagerMobile = ManagerMobile

   FROM HumanResources.Employee

   WHERE EmployeeID = @employeeID

END

S K replied to msakt on 12-Jun-12 07:01 AM
If you want to return more than one value from stored proc than you should use output paramter so you sp lookes like this

Create porc spname
(
@count1 int out,
@count2 int out
)
AS
Begin
select @count1  = count(GroupName) from GroupCustomerManagement where BusinessID = 9
select @count2 = count(GroupName) from GroupCustomerManagement where BusinessID = 9 and Active =1 

End
TSN ... replied to msakt on 12-Jun-12 07:03 AM

Here is the sample for you…

 

CREATE PROCEDURE GetImmediateManager

@employeeID INT,

@Count1 INT OUTPUT

@Count2 INT OUTPUT

AS

BEGIN

select @Count1=count(GroupName) from GroupCustomerManagement where BusinessID = 9
select @Count2=count(GroupName)from GroupCustomerManagement where BusinessID = 9 andActive=1

End

Jitendra Faye replied to msakt on 12-Jun-12 07:07 AM
You can try like this-

SELECT col1, col2 from @tab1
UNION
SELECT  col1, col2 from @tab2

Try this and let em know.
Raman S V replied to msakt on 12-Jun-12 07:10 AM

Hi,

PFB the required code sample. Have 2 parameters as out parameters.

There is no need to pass any values while executing the procedure.



Create
proc TestProc

(

@recordCountTable1 int out,

@recordCountTable2 int out

)

as

begin

    select @recordCountTable1=COUNT(*) from Table1

    select @recordCountTable2=COUNT(*) from Table2

    return

end



--The below query is to execute the procedure during execution

declare @recordCountTable1 int

declare @recordCountTable2 int

exec TestProc @recordCountTable1 out,@recordCountTable2 out

select @recordCountTable1,@recordCountTable2

Regards,
Raman S V