Link to home
Create AccountLog in
Avatar of techques
techques

asked on

How to write VC# code to call a Functions in MSSQL 2005 server?

Hi

I write a function in MSSQL 2005 DB, how should I write the VC# code to get the return value?

Here is the C# code to get return value from a Stored Procedure.

But, how can I change it to get value from a Functions?
VC#:
public int SP_GetMemberStatus(int id)
        {
            try
            {
                using (SqlConnection Connection = new SqlConnection(ConnectionString))
                {
                    string commandstring = "SP_GetMemberStatus";
 
                    using (SqlCommand command = new SqlCommand(commandstring, Connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandTimeout = 15;    
                        command.Parameters.Add("@userid", SqlDbType.Int).Value = id;
                        command.Parameters.Add("@status", SqlDbType.Int).Direction = ParameterDirection.Output;
                        Connection.Open();
                        command.ExecuteNonQuery();
                        return Convert.ToInt32(command.Parameters["@status"].Value);
                    }
                }
            }
            catch (SqlException ex)
            {
                string e = ex.Message;
                return 0;
            }
        }

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of daveamour
daveamour
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
A function is only used in the context of a query or a command inside a stored procedure. If you want the output of a single function call, you can do something like this:

select top 1 myfunction() from xtable
SOLUTION
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Ok looks like I was wrong, my apologies.
That explains just about the same I put down, luckily with some source code. I wasn't aware that you could remove the "from xtable". Nice
Avatar of techques
techques

ASKER

I think using SP to call function is more easier for me. As those examples are VB but not C#. I do not quit understand. Thanks for help.