In SQL Server, SELECT returns only one column

Question

I use SQL Server 2008 Express. I have a database with a table Names. This table has only 3 columns: ID, FirstName, LastName.

I created a stored procedure:

CREATE PROCEDURE GetName AS
  SELECT 
      ID, FirstName, LastName 
  FROM Names

I have three records in the table. When I execute the query, the output shows only the column ID. No matter what combination of columns I choose, the query always displays one column.

Some clarifications:

I use MS Visual Web Developer 2010, language C#. I try to execute query with CTRL+ALT+F5 (Execute command from context menu). The columns are correctly designed.

I run the query in MSSQL Management Studio 2008: USE namesDB; EXEC GetNames. It works correctly (all three columns are displayed). But when I try to Execute it in MS WEB DEVELOPER, returns, it show, as I mentioned, only one column.

I have only the table I mentioned. Every field is filled. There is no empty column.

Where I am wrong ?

Thank you

The problem appears when you **run the query** or only **execute the procedure**? — Alexander, Feb 14 '14 at 20:41
running the script you posted will create the procedure, now try EXEC GetName — Mark Giaconia, Feb 14 '14 at 20:43
You don't have a `where` statement in the query. How are you choosing anything? — Gordon Linoff, Feb 14 '14 at 20:47
This will return three columns. How are you executing it? In management studio? Presumably not. If your own application show the relevant code. — Martin Smith, Feb 14 '14 at 20:55
when you run the procedure as `exec GetName`; what is the result you get? — Rahul, Feb 14 '14 at 20:57
@logixologist same name as what? The procedure? Only if in a different schema and the way of calling the two things is totally different. `exec` vs `select` — Martin Smith, Feb 14 '14 at 21:03
Do you mean to say the column names `FirstName` and `LastName` don't appear in the output, or that the column _values_ are empty? Are you sure there is data in the table? — agentnega, Feb 14 '14 at 21:06
@MartinSmith I was thinking perhaps he could have a table called dbo.names and a view called dbo.names. — logixologist, Feb 14 '14 at 21:06
Again not possible. Stuff in `sys.objects` can't have the same schema and object name even if different types. And even if there was an object with fewer columns in a different schema you would get an invalid column name error about the missing columns. It wouldn't just ignore them and return the one column. — Martin Smith, Feb 14 '14 at 21:08
thanks @MartinSmith I was trying to think of any way where his input was reduced to only one column. — logixologist, Feb 14 '14 at 21:11
The columns names FirstName and LastName don't appera in the output, — E B, Feb 14 '14 at 21:30
The tables is not empty: I have 2 records, every filed is filled.. N — E B, Feb 14 '14 at 21:31
I run the queryin MSSQL Management Studio 2008: USE namesDB; EXEC GetNames. It works correctly (all three columns are displayed). But when I try to Execute it in MS WEB DEVELOPER, returns, it show, as I mentioned, only one column. — E B, Feb 14 '14 at 21:51
E B, you should add that information to the question as it could very well be relevant and we don't want to leave it buried down here in the comments. — agentnega, Feb 14 '14 at 22:03

agentnega · Answer 1 · 2014-02-14T21:08:05.080

create table t (ID int primary key, FirstName nvarchar(10), LastName nvarchar(10));
insert into t values (0, 'Adam','Ant');
insert into t values (1, 'Bob', 'Barker');
insert into t values (2, 'Charlie', 'Chaplin');

create procedure GetName as select ID, FirstName, LastName from t;

exec GetName

Result Set (3 items)
ID FirstName LastName 
0 Adam Ant 
1 Bob Barker 
2 Charlie Chaplin

Works for me (mind you, using SQL Server 2012 and LINQPad). You must somehow not be doing what you think you're doing. Or perhaps there is no data in your table?

In SQL Server, SELECT returns only one column

1 Answers1