· 8 years ago · Jan 31, 2018, 04:36 PM
1Stack Overflow
2Questions
3Developer Jobs
4Tags
5Users
6
7Search…
8
9
108
11â—4
12SQL Server - SELECT FROM stored procedure
13Ask Question
14
15up vote
16214
17down vote
18favorite
1936
20I have a stored procedure that returns rows:
21
22CREATE PROCEDURE MyProc
23AS
24BEGIN
25 SELECT * FROM MyTable
26END
27My actual procedure is a little more complicated, which is why a sproc is necessary.
28
29Is it possible to select the output by calling this procedure?
30
31Something like:
32
33SELECT * FROM (EXEC MyProc) AS TEMP
34I need to use SELECT TOP X, ROW_NUMBER, and an additional WHERE clause to page my data, and I don't really want to pass these values as parameters.
35
36sql sql-server sql-server-2005 stored-procedures
37shareedit
38edited Sep 19 '12 at 18:31
39
40the Tin Man
41126k27159231
42asked Sep 29 '09 at 13:05
43
44jonathanpeppers
4516k1683168
46
47I'm unsure as to what you intend to do here because when you execute the procedure, you are getting the rows back. Is it that you want to execute the procedure inside a SELECT statement so you can tie it to a pageable object? – Raj More Sep 29 '09 at 13:11
481
49Is there a particular reason why you don't want to pass the values as parameters? To do it the way you are suggesting is a bit inefficent - you would be selecting more data than you need, and then not using it all. – Mark Bell Sep 29 '09 at 13:13
502
51Take a look at here: sommarskog.se/share_data.html – pylover May 9 '12 at 10:51
52add a comment
5315 Answers
54active oldest votes
55up vote
5698
57down vote
58accepted
59You can use a User-defined function or a view instead of a procedure.
60
61A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a SELECT statement.
62
63shareedit
64edited Jul 17 '17 at 13:29
65
66Kolappan Nathan
6720967
68answered Sep 29 '09 at 13:13
69
70Mehrdad Afshari
71317k69748734
726
73Additionally, if after converting to a UDF you find you need the stored procedure semantics you can always wrap the UDF with a procedure. – Joel Coehoorn Sep 29 '09 at 13:26
74
75what if, we need to send parameters to mulple stored procedures and combine them into one one big stored procedure? Can view, take parameters, like stored procedures does – mrN Aug 18 '11 at 7:14
763
77@mrN Views don't take parameters, but UDFs do. – Mehrdad Afshari Aug 18 '11 at 8:26
782
79Hello, I really need to do this without converting the sp to a view or function, is it possible? – Luis Becerril Mar 7 '17 at 17:46
80
81@LuisBecerril Same here. The underlying sproc is protected and I have no permission to change it (or even view the script) – jf328 Jul 10 '17 at 21:39
82add a comment
83
84up vote
8561
86down vote
87You either want a Table-Valued function or insert your EXEC into a temporary table:
88
89INSERT INTO #tab EXEC MyProc
90shareedit
91answered Sep 29 '09 at 13:11
92
93CMerat
943,3761821
9521
96The problem with INSERT #T or INSERT @T is that an INSERT EXEC statement cannot be nested. If the stored procedure already has an INSERT EXEC in it, this won't work. – MOHCTP May 30 '13 at 1:44
97add a comment
98up vote
99110
100down vote
101You can
102
103create a table variable to hold the result set from the stored proc and then
104insert the output of the stored proc into the table variable, and then
105use the table variable exactly as you would any other table...
106... sql ....
107
108Declare @T Table ([column definitions here])
109Insert @T Exec storedProcname params
110Select * from @T Where ...
111shareedit
112edited Dec 20 '16 at 17:11
113answered Sep 29 '09 at 13:11
114
115Charles Bretana
116103k17113190
11720
118The problem with INSERT #T or INSERT @T is that an INSERT EXEC statement cannot be nested. If the stored procedure already has an INSERT EXEC in it, this won't work. – MOHCTP May 30 '13 at 1:44
1191
120This probably the most portable solution, being closest to basic SQL. It also helps to maintain strong column type definitions. Should have more upvotes than those above. – user2074102 Aug 8 '14 at 18:58
121
122The table variables looks more useful here than temporary tables in terms of sp recompile. So I agree, this answer should have more upvotes. – resnyanskiy Mar 16 '16 at 5:21
123add a comment
124up vote
1252
126down vote
127It sounds like you might just need to use a view. A view allows a query to be represented as a table so it, the view, can be queried.
128
129shareedit
130answered Sep 29 '09 at 13:12
131
132Lawrence Barsanti
13311.6k83557
134add a comment
135up vote
13617
137down vote
138You can copy output from sp to temporaty table.
139
140CREATE TABLE #GetVersionValues
141(
142 [Index] int,
143 [Name] sysname,
144 Internal_value int,
145 Character_Value sysname
146)
147INSERT #GetVersionValues EXEC master.dbo.xp_msver 'WindowsVersion'
148SELECT * FROM #GetVersionValues
149drop TABLE #GetVersionValues
150shareedit
151answered Sep 29 '09 at 13:12
152_Seba_
153add a comment
154up vote
155125
156down vote
157You should look at this excellent article by Erland Sommarskog:
158
159How to Share Data Between Stored Procedure
160It basically lists all available options for your scenario.
161
162shareedit
163answered Sep 29 '09 at 13:16
164
165kristof
16632k2170103
1672
168This should really be the accepted answer. The article referenced is very thorough. – ssmith Feb 24 '10 at 16:46
1691
170Great reference, I can see myself coming back to that for a long time. – Adam Neal Jan 25 '12 at 20:55
171
172Excellent answer, thanks! – Sanjiv Jivan May 23 '17 at 14:46
1731
174@ssmith Well, except that links to answers aren't really answers, they're directions to an answer. Would be great to move some of that info, especially if the blog author gives permission, into this answer. – ruffin Oct 17 '17 at 20:12
175add a comment
176up vote
1774
178down vote
179You can cheat a little with OPENROWSET :
180
181SELECT ...fieldlist...
182FROM OPENROWSET('SQLNCLI', 'connection string', 'name of sp')
183WHERE ...
184This would still run the entire SP every time, of course.
185
186shareedit
187edited Jul 13 '12 at 13:29
188
189marc_s
190524k11410151176
191answered Sep 29 '09 at 13:21
192
193MartW
19410.5k23660
195add a comment
196up vote
19733
198down vote
199You must read about OPENROWSET and OPENQUERY
200
201SELECT *
202INTO #tmp FROM
203OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
204shareedit
205answered Feb 22 '12 at 19:06
206
207Rizwan Mumtaz
2081,92411622
209add a comment
210up vote
21130
212down vote
213It is not necessary use a temporary table.
214
215This is my solution
216
217SELECT * FROM
218OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
219WHERE somefield = anyvalue
220shareedit
221edited Sep 16 '14 at 8:37
222
223slavoo
2243,322122632
225answered Jan 15 '14 at 15:12
226
227DavideDM
2281,1191216
2291
230This needs you to add your server as a linked server to itself, but it works like a charm! thanks! – vaheeds Nov 16 '16 at 9:42
231
232Some great caveats on this: stackoverflow.com/questions/2374741/… – Keith Adler May 19 '17 at 7:57
233
234Hmm ... I am getting the error "Error 7411: Server 'YourServerName' is not configured for DATA ACCESS." What do I need to change? – Matt Dec 5 '17 at 9:21
235
236Have you add your server as a linked server? YourServerName is the name of your server. You have to change YourServerName with your real server name. – DavideDM Dec 5 '17 at 10:50
237add a comment
238up vote
2392
240down vote
241Try converting your procedure in to an Inline Function which returns a table as follows:
242
243CREATE FUNCTION MyProc()
244RETURNS TABLE AS
245RETURN (SELECT * FROM MyTable)
246And then you can call it as
247
248SELECT * FROM MyProc()
249You also have the option of passing parameters to the function as follows:
250
251CREATE FUNCTION FuncName (@para1 para1_type, @para2 para2_type , ... )
252And call it
253
254SELECT * FROM FuncName ( @para1 , @para2 )
255shareedit
256edited Apr 23 '15 at 18:44
257
258Phillip Senn
25917.1k67202314
260answered Feb 19 '15 at 13:06
261
262al_the_man
263912
264add a comment
265up vote
26612
267down vote
268You need to declare a table type which contains the same number of columns your store procedure is returning. Data types of the columns in the table type and the columns returned by the procedures should be same
269
270 declare @MyTableType as table
271 (
272 FIRSTCOLUMN int
273 ,.....
274 )
275Then you need to insert the result of your stored procedure in your table type you just defined
276
277Insert into @MyTableType
278EXEC [dbo].[MyStoredProcedure]
279In the end just select from your table type
280
281Select * from @MyTableType
282shareedit
283answered Dec 18 '15 at 14:26
284
285Aamir
286443412
287
288That is the best solution for me, because you don't need to specify the server name, connection strings or have to configure any linked servers in order to make it work - which are things I don't want to do to just to get some data back. Thank you! Awsome answer! – Matt Dec 5 '17 at 9:27
289add a comment
290up vote
2914
292down vote
293use OPENQUERY and befor Execute set 'SET FMTONLY OFF; SET NOCOUNT ON;'
294
295Try this sample code:
296
297SELECT top(1)*
298FROM
299OPENQUERY( [Server], 'SET FMTONLY OFF; SET NOCOUNT ON; EXECUTE [database].[dbo].[storedprocedure] value,value ')
300shareedit
301answered Aug 25 '16 at 7:38
302
303Ali asghar Fendereski
304534
305add a comment
306up vote
3070
308down vote
309If your server is called SERVERX for example, this is how I did it...
310
311EXEC sp_serveroption 'SERVERX', 'DATA ACCESS', TRUE;
312DECLARE @CMD VARCHAR(1000);
313DECLARE @StudentID CHAR(10);
314SET @StudentID = 'STUDENT01';
315SET @CMD = 'SELECT * FROM OPENQUERY([SERVERX], ''SET FMTONLY OFF; SET NOCOUNT ON; EXECUTE MYDATABASE.dbo.MYSTOREDPROC ' + @StudentID + ''') WHERE SOMEFIELD = SOMEVALUE';
316EXEC (@CMD);
317To check this worked, I commented out the EXEC() command line and replaced it with SELECT @CMD to review the command before trying to execute it! That was to make sure all the correct number of single-quotes were in the right place. :-)
318
319I hope that helps someone.
320
321shareedit
322answered Oct 4 '16 at 3:32
323
324Fandango68
3251,41411432
326add a comment
327up vote
3281
329down vote
330If 'DATA ACCESS' false,
331
332EXEC sp_serveroption 'SQLSERVERNAME', 'DATA ACCESS', TRUE
333after,
334
335SELECT * FROM OPENQUERY(SQLSERVERNAME, 'EXEC DBNAME..MyProc @parameters')
336it works.
337
338shareedit
339edited Jun 7 '17 at 19:44
340answered May 4 '17 at 9:19
341
342Ali Osman Yavuz
343664
344add a comment
345up vote
3460
347down vote
348For the sake of simplicity and to make it re-runnable, I have used a system StoredProcedure "sp_readerrorlog" to get data:
349
350-----USING Table Variable
351DECLARE @tblVar TABLE (
352 LogDate DATETIME,
353 ProcessInfo NVARCHAR(MAX),
354 [Text] NVARCHAR(MAX)
355)
356INSERT INTO @tblVar Exec sp_readerrorlog
357SELECT LogDate as DateOccured, ProcessInfo as pInfo, [Text] as Message FROM @tblVar
358
359
360
361-----(OR): Using Temp Table
362IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
363CREATE TABLE #temp (
364 LogDate DATETIME,
365 ProcessInfo NVARCHAR(55),
366 Text NVARCHAR(MAX)
367)
368INSERT INTO #temp EXEC sp_readerrorlog
369SELECT * FROM #temp
370shareedit
371edited Sep 15 '17 at 19:21
372
373KirstieBallance
374188112
375answered May 4 '17 at 13:08
376
377Sheikh Kawser
3783616
379add a comment
380Your Answer
381Links Images Styling/Headers Lists Blockquotes Code HTML advanced help »
382
383
384Post Your Answer
385Not the answer you're looking for? Browse other questions tagged sql sql-server sql-server-2005 stored-procedures or ask your own question.
386asked
387
3888 years, 4 months ago
389
390viewed
391
392596,365 times
393
394active
395
3964 months ago
397
398FEATURED ON META
399What criteria should we use to determine which review queue indicator a site…
400HOT META POSTS
4019 Automatic syntax highlighting for Spark Python & R APIs (PySpark & SparkR)
40235 Including other people's answer into your own accepted answer
4039 Comment option disappears after deleting comment
40430 Add an assessment to your Developer Story
405Work from anywhere
406Junior Innovation Developer
407Roth Technical Specialties Inc.No office location
408$86K - $115KREMOTE
409javascriptnode.js
410Full-stack JavaScript Developer [React/Node.js]
411AulaNo office location
412£40K - £70KREMOTE
413javascriptnode.js
414Manager, Front End Engineering (Remote)
415ZapierNo office location
416REMOTE
417node.jsecmascript-6
418Blockchain Expert and Smart Contract Developer
419DyverseNo office location
420REMOTE
421c#java
422Work remotely - from home or wherever you choose.
423
424Browse remote jobs
425Linked
4262
427How to execute a stored procedure within a SELECT clause in SQL Server
4281
429How to get only Table Name using Sp_depends?
4300
431Dynamically query a temp table created in a stored procedure
4321
433How can I use a select statement in conjuction with a stored procedure
4341
435Querying the resultset of a stored proc
4360
437SQL Server - Call stored procedure in a IN Statement
4381
439Put a where clause around a stored procedure I cannot edit
44029
441Creating table variable in SQL server 2008 R2
4426
443Why is using OPENQUERY on a local server bad?
444-1
445SELECT from stored procedure inside a view
446see more linked questions…
447Related
4482783
449How can I prevent SQL injection in PHP?
4502141
451Add a column with a default value to an existing table in SQL Server
4521266
453How to return only the Date from a SQL Server DateTime datatype
4541469
455How to check if a column exists in SQL Server table
456380
457Select columns from result set of stored procedure
4581260
459Insert results of a stored procedure into a temporary table
460253
461Select n random rows from SQL Server table
462600
463Function vs. Stored Procedure in SQL Server
4642792
465How do I UPDATE from a SELECT in SQL Server?
466505
467Search text in stored procedure in SQL Server
468Hot Network Questions
469Diluted Integer Sums
470Unexpected behaviour in ruby for "puts {}.class"
471Sentences are crossing the margin. Is there any solution?
472Can a subsequence repeat a term finitely many times?
473How can I politely ask my date to not use her phone unnecessarily during dinner, without ruining the night out?
474Check if all non-zero elements in a matrix are connected
475Why can't a Tercio use crossbows and pikes?
476How many slaves do I need to keep my mountain palace supplied?
477Can the stock price go up even if no one is buying?
478Interface initiailization
479Will a Linux executable compiled on one "flavor" of Linux run on a different one?
480Is a Dungeon Master allowed to change existing rules in Adventurers League play?
481Why does it say on Netflix that "Star Trek: Discovery" is a Netflix original series?
482What happened to the humans in "Cars" (that is if they ever existed)?
483How is verbatim implemented?
484When is it inappropriate to control for a variable?
485What would human survivors eat in a post apocalyptic Nuclear winter?
486Defining a variable and its static equivalent in the same function (C/C++)
487Making code findable by using globally unique message IDs
488How can I convince my partner to accept my best friend instead of wanting to break up?
489When does excessive collaboration become plagiarism?
490How to balance the Zealot in a setting without resurrection?
491Which key signature to pick for this chord progression?
492Should I use Bonjour or Salut to a clerk in France?
493 question feed
494STACK OVERFLOW
495Questions
496Jobs
497Developer Jobs Directory
498Salary Calculator
499Help
500Mobile
501STACK OVERFLOW
502BUSINESS
503Talent
504Ads
505Enterprise
506COMPANY
507About
508Press
509Work Here
510Legal
511Privacy Policy
512Contact Us
513STACK EXCHANGE
514NETWORK
515Technology
516Life / Arts
517Culture / Recreation
518Science
519Other
520Blog Facebook Twitter LinkedIn
521site design / logo © 2018 Stack Exchange Inc; user contributions licensed under cc by-sa 3.0 with attribution required. rev 2018.1.31.28679