select from sp
-
Is there an easy way to select specific fields from a stored procedures result fields.
I was thinking something like this:
select [name] from exec SelectStuff
-
Hi Hoila,
quote:
Is there an easy way to select specific fields from a stored procedures result fields.I was thinking something like this:
select [name] from exec SelectStuff
What are you trying to do?
In general it is better to only select the fields you are interested in.
You can also do a SELECT * and figure the desired fields out with your client application
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I would like to set some of the fields returned from the sp into variables, but not all of them.
The original sp is used to get all the data. From my other sp i would like to get only some fields from the original sp.
It is possible to use a temp table or a table variable, but I think there should be an easier way.
-
Hoila,
If the original SP contains a number of fields you are not interested in, then you are going to have to watch for duplicates in the set of fields you are interested in.
ie. output from SP1
ID, Field1 , Field2 , Field3 , Field4
1 , Value11, Value12, Value13, Value14
2 , Value11, Value22, Value33, Value14
If you are only interested in fields Field1 & Field4, you are going to get duplicate rows.
You may find it easier to write a second SP that will only return the fields you are interested in (a lot less heartache).
John
John Oliver
Computershare Technology Services
Abbotsford, Victoria, Australia
-- John Oliver
Sometimes banging your head against a wall is the only solution. -
Configure your server for Openquery
1) sp_addlinkedserver SERVER_NAME
2) EXEC sp_serveroption 'SERVER_NAME', 'data_access', 'true'
Then run:
3) select col2, col1 from openquery(SERVER_NAME, 'exec DB_NAME..SelectStuff')
-
The original sp does many things so I don´t want to have that code in 2 sp´s and the sp only returns one row of data (it could return many but I am still just using the first row).
The openquery option would work but it seems a little wierd to use the local server as a remote one?!
Now I have used a temp table to make it work, maybe it´s a little overkill, but it works. Thanks for the answers and the thoughts.
-
You could take the results of the stored procedure and insert them into a temporary table. Then you can query the table yourself and get the columns you want.
Eg: spProc returns columns A varchar (50), B datetime and C int
Create table #temp (A1 varchar (50), B1 datetime, C1 int)
Insert into #temp (A1, B1, C1)
exec spProc
select * from #temp
drop table #temp
Soemthing like that should work.
-
I might not be on the right track with this, but how about you execute your sp into a temp table, and then just select the data out of the temp table.
I'm not sure if the following code will help you, but it at least gives you an idea...
use northwind
CREATE TABLE #TempTable (
Field1 varchar(50),
qty int )
INSERT INTO #TempTable exec custorderhist ALFKI
SELECT sum(qty) FROM #TempTable
drop table #TempTable -
quote:
Configure your server for Openquery1) sp_addlinkedserver SERVER_NAME
2) EXEC sp_serveroption 'SERVER_NAME', 'data_access', 'true'
Then run:
3) select col2, col1 from openquery(SERVER_NAME, 'exec DB_NAME..SelectStuff')
I don't suggest this method as it does create some extra overhead issues on connections to the box.
IF you are never accesing the reusable SP directly then I suggest use the method presented by BradleyB.
But on a personal note the creation and destruction of objects like temp tables create a considerable amount of overhead on the IO and memory especially for large tables. I suggest personally use the reusable SP as base concept and create new SPs based on it with their own code and avoid the temp table. This is the most efficient way to proceed plus statistics and execution plans are not stored for the temp table so you take a performance hit there as opposed to doing the direct code to the tables involved themselves. And you only return exactly what you need thru memory unlike the other way.
-
quote:
I suggest personally use the reusable SP as base concept and create new SPs based on it with their own code and avoid the temp table. This is the most efficient way to proceed plus statistics and execution plans are not stored for the temp table so you take a performance hit there as opposed to doing the direct code to the tables involved themselves. And you only return exactly what you need thru memory unlike the other way.Reusable sp's are a great concept - unfortunately they don't exist.
If what is meant is to 'cut' & 'paste' from one sp to another then that's called duplication of code which leads to redundancy which of course leads to extra maintenance.
-
quote:
quote:
I suggest personally use the reusable SP as base concept and create new SPs based on it with their own code and avoid the temp table. This is the most efficient way to proceed plus statistics and execution plans are not stored for the temp table so you take a performance hit there as opposed to doing the direct code to the tables involved themselves. And you only return exactly what you need thru memory unlike the other way.Reusable sp's are a great concept - unfortunately they don't exist.
If what is meant is to 'cut' & 'paste' from one sp to another then that's called duplication of code which leads to redundancy which of course leads to extra maintenance.
You are right, but some folks also call them template SPs, and yes the maintainence can be a nightmare but the overhead on the server can create other issues. ALso, having a common SP they access the way previously descirbed still create coding madness if something comes up and you end up making a change to the multiused SP, now you have to go thru all to make sure will not cause errors. Unfortunately SQL is all about coding. I am hoping in the future they will put a class type mechanism in so you can create more functional reusable code (UDFs are a start).
-
I would reuse the existing SP in a simpler way: write a new SP with the same input parameters as the original , and only the required output parameters. The only thing the new SP should do is call to the original one.
-
If a DTS package suits your purpose (or maybe it does and you don't know it yet
) - transformations from a SQL Server can use sp's. For example, if you're creating a simple basic transformation between to SQL databases you get the option of using an entire table as source data or writing some SQL to get your source data. Go for the SQL and put your stored procedure in there. Then you'll get the flexibility of mapping/transformations. Other than that, I like selims idea as a temporary fix- but would be hesitant to rely on that scenario permanently in production.
-Ken
) - transformations from a SQL Server can use sp's. For example, if you're creating a simple basic transformation between to SQL databases you get the option of using an entire table as source data or writing some SQL to get your source data. Go for the SQL and put your stored procedure in there. Then you'll get the flexibility of mapping/transformations. Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply