Just one column from a stored procedure

Question

I have this stored procedure in my SQL Server;

CREATE PROC GetChild
    @Child_ID int
AS
    SELECT * FROM Children WHERE Child_ID = @Child_ID

I am calling this stored procedure from C#.

I would like to know, if it is possible to call just one column from this table instead of the whole record from C#.?

dont forget to mark answer as accepted if you got the info you want......... — Pranay Rana, Feb 27 '12 at 19:27

score 2 · Answer 1 · edited May 23 '17 at 11:48

You have only have three choices.

Rewrite the Stored procedure to just return the columns you want.

e.g. SELECT foo from Children Where Child_id = @Child_ID
Use a DataReader and just get the columns you want from that

Using a reader directly

while (reader.Read())
```
  `Console.WriteLine("{0}", reader.GetInt32(0));`
```
Using the Linq extension methods which allows you to filter and sort the results as well as getting just the columns you want.

var List = rdr.Cast<IDataRecord>() .Select(s => s.GetInt32(0)).ToList();
Abandon the stored procedure and write Select statements against the table. See Pranay's answer

@Ryan see my post for a full example. – Feb 27 '12 at 16:56 — , Feb 27 '12 at 16:56

score 2 · Answer 2 · answered Feb 27 '12 at 16:54

Assuming you mean return one column, if this is what your stored procedure looks like then no. It will always return all columns back to the client.

You can simply ignore the returned columns that you do not need. Or you can change the stored procedure to only return one column. But as is, it always returns all of them.

Pranay Rana · Answer 3 · 2012-02-27T19:26:17.510

just write below query

select columnname from Children where Child_ID = @Child_ID

columnname- is name of the column you want to retrive

Code for you

SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText ="select columnname from Children where Child_ID = @Child_ID";

 mySqlCommand .Parameters.Add("@Child_ID", SqlDbType.Int);
 mySqlCommand .Parameters["@Child_ID"].Value = idvalue;
 mySqlConnection.Open();

 SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SingleRow);


while (mySqlDataReader.Read()){
  Console.WriteLine("mySqlDataReader[\" columnname\"] = " +
    mySqlDataReader["columnname"]);
}

mySqlDataReader.Close();
mySqlConnection.Close();

score 0 · Answer 4 · answered Feb 27 '12 at 16:55

Use a SqlDataReader for this:

SqlConnection DbConn = new SqlConnection(YourConnStringHere);
SqlCommand ExecStoredProc = new SqlCommand();
ExecStoredProc.CommandType = CommandType.StoredProcedure;
ExecStoredProc.CommandText = "GetChild";
ExecStoredProc.Connection = DbConn;
ExecStoredProc.Parameters.AddWithValue("@ChildID", YourChildId);

using (DbConn)
{
    DbConn.Open();
    using (SqlDataReader sdr = ExecStoredProc.ExecuteReader())
    {
        while(sdr.Read())
            // reference your column name like this:
            // sdr.GetString(sdr.GetOrdinal("YourColumnName"));
    }
}

You can reference any column returned by the SqlDataReader.Read() method. Likewise, if you are looking for an integer value:

int someInt = sdr.GetInt32(sdr.GetOrdinal("YourColumnName"));

score 0 · Answer 5 · edited May 23 '17 at 11:48

From this thread ( Insert results of a stored procedure into a temporary table ), you might want to try OPENROWSET.

First, configure your DB,

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

then, depending on your connection :

SELECT  yourcolumnname
  FROM    
OPENROWSET(
      'SQLNCLI', 
      'server=yourservername;database=yourdbname;uid=youruserid;pwd=youruserpwd',
      'EXEC [GetChild] yourchildid'
       )

or

SELECT  yourcolumnname
  FROM    
OPENROWSET(
      'SQLNCLI', 
      'server=yourservername;database=yourdbname;Trusted_Connection=yes',
      'EXEC [GetChild] yourchildid')

I wouldn't use this solution when retrieving only one line. Performance would be really bad. For retrieving a great number of lines, this should do the job.

Just one column from a stored procedure

5 Answers5