asked
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.
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?
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.
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.
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.
@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.
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.
20 People are following this question.
