Why Stored Procedure cannot be used with Select, Where & Having. Understand function can serve the purpose. Why SP cannot

Why stored procedure cannot be used with Select, Where & Having?

I understand a function can serve the purpose - what's the reason why a stored procedure cannot be executed in select?

Blogs are answering you can use UDF. Understand we can use UDF & we are using. Need a valid reason on stored procedure.

more ▼

asked Apr 08, 2017 at 06:30 AM in Default

avatar image

11 1

Can you clarify? Are you asking why you can't use a stored procedure inline with a select statement or a where/having clause?

Apr 09, 2017 at 01:20 AM JohnM
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

A stored procedure is a module of code. It does 'something' and may or may not return anything. It may also return multiple data sets.

So really you can't select from it, because it isn't an object that you can select from. You can return the results of a stored procedure into a table, and then select from that. Does that help?

more ▼

answered Apr 11, 2017 at 02:50 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left

Like the man said, that's the way MS designed it. There is no other reason.

If you want to get a bit fancy, you could use OPENROWSET to return the results of your stored procedure as if it were a table.

more ▼

answered May 09, 2017 at 05:02 AM

avatar image

Jeff Moden
2.3k 3 7 13

@PradeepLakhina Or you can try the "old school" way of creating a temp table and then using the

 insert into #t exec YourProc;

If the procedure is returning a set of data (and of course has set nocount on option set, but this one goes without saying, everyone has it in every stored procedure definition, right?) then once the table is populated with data returned from the stored procedure, you can use the temp table in your select.

If the procedure has multiple selects in it then inserting into the temp table will not work unless every select has the "same DNA" so to speak, that is the columns' number and type in every select is the same.

May 09, 2017 at 03:12 PM Oleg
(comments are locked)
10|1200 characters needed characters left

Dear Sir,

Thats too applies on function too. it is upto the filter whether function will return data or not. Can u give me the reason why stored procedure are not allowed to work with select , where & having.

more ▼

answered Apr 14, 2017 at 06:25 AM

avatar image

11 1

Not really. A function always returns a data set - it may be empty or NULL, but it still has the same 'shape' (number of columns). So the database engine can parse queries that select from it, as it knows upfront what is expected out of the function.

A stored procedure does not have this fixed structure. It might not return any data (and I don't mean an empty data set based on a filter, I mean no data - think of an insert statement in a SP, with SET NOCOUNT ON - all you get back is 'Command(s) completed successfully.' Or it may return multiple data sets, or a different one each time.

May 14, 2017 at 09:29 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

There is a fairly significant difference between Stored Procedures (as well as Triggers) and UDFs (both Table-valued and Scalar, as well as Views and Tables):Stored Procedures (as well as Triggers) lack a single, consistent result set, and they can change the state of the database.

The lack of a single, consistent result set (i.e. always returning the same columns of the same datatypes) probably makes it impossible for the query optimizer to know how to handle them when processing the query internally. The optimizer can (and does) move query elements around; queries describe the results you are wanting, not how to get those results, and are not guaranteed to be executed exactly as you have written them. So, the query optimizer decides when, and how frequently, to execute functions. Meaning, a function might run per row, or might run once and have its return value cached. Or, a function can be moved into, or out of, a subquery, etc.

The ability to change the state of the database is especially problematic because it takes away the ability of the query optimizer to guarantee the state of the data for the query that it is currently processing. Meaning, if you can change the data while rows are being joined and/or filtered, then are those results valid / accurate? You might know better than to make changes to data that is part of the current query, but SQL Server can't be certain that nobody will ever do something that they shouldn't.

Both of these issues would allow for queries that produce odd and/or inconsistent results that are also very hard to debug. And likely this also breaks ACID compliance.

more ▼

answered May 16, 2017 at 04:54 PM

avatar image

488 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 08, 2017 at 06:30 AM

Seen: 194 times

Last Updated: May 16, 2017 at 04:54 PM

Copyright 2018 Redgate Software. Privacy Policy