question

pailwriter avatar image
pailwriter asked

Returning only certain columns from a stored procedure

In my efforts to learn T-SQL, (studying for the 70-461 exam) I came across a stored procedure that returns 39 columns of information about a table.

I only want to return 5 of those columns.

Here is the stored procedure I am working with.

EXEC sp_describe_first_result_set N’SELECT * FROM Sales.Customers;’;

As mentioned, this returns 39 columns of data.

I only wanted to return 5 of those columns so I modified the “SELECT” statement in the stored procedure as this:

EXEC sp_describe_first_result_set N’SELECT column_ordinal, name, is_nullable, 
system_type_name, max_length FROM Sales.Customers;’;

These are 5 of the 39 columns returned with the first EXEC stored procedure.

However, when I run my modified version (to return only the 5 columns I want) I get an error, “Invalid Column Name” for each column.

Is this not possible to do, or am I doing it wrong.

P.S. I’m running this query against the TSQL2012 database while studying the “Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012” book.

Thanks in advance for your support.

stored-procedurescolumnstored procedureexec
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

The columns you have selected are actually the output columns from "sp_describe_first_result_set".

What I am guessing you are wanting is that information for all columns in Sales.Customers?

What you would need to do is to run an INSERT INTO ... EXEC … command, then select the 5 columns from the temp table.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pailwriter avatar image
pailwriter answered

Thanks Anthony. That process was going the long way around, but it did work.

In case anyone else want’s the code that I used to get the results; here it is.

First, I ran the EXEC command to retrieve the column names of all columns.

EXEC sp_describe_first_result_set N'SELECT * FROM Sales.Customers;';

Next, I hand typed them out one at a time. I used that list to make a table to dump the data into and I provided appropriate datatypes to each column. Here is the list to create the new table.

CREATE TABLE Sales.SalesTableData(
is_hidden BIT
, column_ordinal INT
, name VARCHAR(100)
, is_nullable BIT
, system_type_id INT
, system_type_name VARCHAR(100)
, max_length INT
, precision INT
, scale INT
, collation_name VARCHAR(MAX)
, user_type_id INT
, user_type_database VARCHAR(100)
, user_type_schema VARCHAR(100)
, user_type_name VARCHAR(100)
, assembly_qualified_type_name VARCHAR(MAX)
, xml_collection_id INT
, xml_collection_database VARCHAR(MAX)
, xml_collection_schema VARCHAR(100)
, xml_collection_name VARCHAR(MAX)
, is_xml_document BIT
, is_case_sesitive BIT
, is_fixed_length_clr_type BIT
, source_server VARCHAR(MAX)
, source_database VARCHAR(MAX)
, source_schema VARCHAR(MAX)
, source_table VARCHAR(MAX)
, source_column VARCHAR(MAX)
, is_identity_column VARCHAR(MAX)
, is_part_of_unique_key BIT
, is_updateable BIT
, is_computed_column BIT
, is_sparse_column_set BIT
, ordinal_in_order_by_list BIT
, order_by_is_descending BIT
, order_by_list_length VARCHAR(MAX)
, tds_type_id INT
, tds_length VARCHAR(MAX)
, tds_collation_id INT
, tds_collatioin_sort_id BIT
);
GO

Then, I ran your suggestion with the INSERT INTO command to populate the data into the new table.

INSERT INTO Sales.SalesTableData
EXEC sp_describe_first_result_set N'SELECT * FROM Sales.Customers;';

Finally, I created a view, returning only the columns I wanted.

CREATE VIEW Sales.vSalesTableData
AS
SELECT 
is_hidden
, column_ordinal
, name 
, is_nullable
, system_type_id
, system_type_name
, is_identity_column
, is_computed_column
FROM Sales.SalesTableData;
GO

Now, I can just do a SELECT * from the view to see the results.

SELECT *
FROM Sales.vSalesTableData;
GO

Thanks so much for your help.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.