Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Server Stored Proc results to Table

Posted on 2011-10-24
Medium Priority
Last Modified: 2012-05-12
Hello.  I have SQL stored proc that returns 5 columns.  I want to execute that SP and place the data into a temp table, but I only want to return 3 of the 5 columns.  Logically I assumed I could do this by defining my temp table with the 3 fields I need, then run an INSERT INTO also specifying the 3 fields.  

Problem is when I run it I receive an error message "Column name or number of supplied values does not match table definition".

Sample code is below.  Any way I can execute the SP and only return a, b, and c into the temp table?

Create Table #TempTable ([a] varchar (20) null,
			 [b] varchar (20) null,
			 [c] varchar (20) null)
Insert Into #TempTable  ([a], [b], [c])

Execute mystoredproc -- this sp returns fields a,b,c,d,e

Open in new window

Question by:jobprojn
  • 2
  • 2
LVL 61

Expert Comment

by:Kevin Cross
ID: 37020228
If I am not mistaken, you will need to insert all the columns and then just drop the ones you don't want later OR just have two tables. One to get initial results and the other to store the three columns you want if going to hold other data. If the table is just holding the stored procedure data, it is probably makes sense to have all the columns and just select a, b, c only or as I said alter table and drop the extraneous columns.

Author Comment

ID: 37020274
mwvisa1, thanks for your comments.  The challenge is that the number of fields in the SP (mystoredproc) is continually changing.  We have numerous other queries/sp's that rely on [mystoredproc] and we would like a way to be able to add fields to [mystoredproc] without having to also update all the many other queries/sp's that use it.  Hope this makes sense.
LVL 61

Accepted Solution

Kevin Cross earned 2000 total points
ID: 37020565
One option if these are singular values is to add OUTPUT parameters. You can have @a, @b, and @c. Another option is to have a single XML parameter that represents the output you want in XML format. You can take that and parse to your table. I know table-valued parameters were added to SQL 2008, but could not remember if OUTPUT was allowed--it is not, but found an interesting option in this thread: http://www.sqlservercentral.com/Forums/Topic627916-145-1.aspx

Suggestion is to create a self referencing linked server, so that you can use OPENQUERY which gives you the ability to then select what columns you want. If your stored procedures all use a common nomenclature for the first three columns this may be a good workaround.

Author Closing Comment

ID: 37020755
Thank you.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question