Hi,

In my application i need to write custom stored procedure something as below:

Storeprocedure getSumOfEmployeeMember
begin

         Select @a=sum(employee) from employeee

         Select @b=sum(member) from member

         return @a/@b

 End

 this SP returns a int value.

How can i call this store procedure and get just one value.........

Regards

Vikas

Perhaps that's just pseudo-code, but I don't really recognize that stored procedure code, and I'm not sure that @a/@b will be an int, or that it won't get a "divide by zero" error. What database are you using? Generally your stored procedure declares any necessary input and output parameters. The return value is a status code, not your desired output. I have not used them, but I believe this is true of CLR stored procedures in SQL Server, as well.

Here's a simple T-SQL stored procedure for SQL Server:

CREATE PROCEDURE [dbo].[proc_GetEmployeeFullName] (
	@ID int,
	@FullName nvarchar(40) output
)
AS
BEGIN

	SET NOCOUNT OFF

	SELECT @FullName = LastName + N', ' + FirstName
      FROM [Employee]
      WHERE EmployeeID = @ID
	
	RETURN @@Error
END

How to write a stored procedure for your database is not a subject we deal with in our forums. There are better places on the Web for that. As far as calling a stored procedure using EntitySpaces, in your custom class you would add a method to call it. For this example, we would put the following method in our Employee.cs custom class:

public string GetFullName(int ID)
{
    esParameters parms = new esParameters();
    parms.Add("ID", ID);
    parms.Add("FullName", esParameterDirection.Output, DbType.String, 40);

    this.ExecuteNonQuery(esQueryType.StoredProcedure, "proc_GetEmployeeFullName", parms);

    return parms["FullName"].Value as string;
}

This would be an example of how you might use it in your app:

Employee emp = new Employee();
emp.LoadByPrimaryKey(1);

string fullName = emp.GetFullName(emp.EmployeeID.Value);

It occurred to me that for single values EntitySpaces also supports ExecuteScalar, which might be simpler in this case. The return is the value in the first column of the first row of the result set.

The T-SQL stored procedure:

CREATE PROCEDURE [dbo].[proc_GetTotalSalaries] 
AS
BEGIN

	SET NOCOUNT OFF

	SELECT SUM(Salary)
      FROM [Employee]
	
END

We put the EntitySpaces calling method in our custom EmployeeCollection class this time.

public decimal GetTotalSalaries()
{
    return (decimal)this.ExecuteScalar(esQueryType.StoredProcedure, "proc_GetTotalSalaries");
}

Usage:

EmployeeCollection collection = new EmployeeCollection();
decimal total = collection.GetTotalSalaries();

yes that was just a pseudo-code.

Thanks for the reply will help a lot.

Regards

Vikas