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.
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.
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.