question

PradeepLakhina avatar image
PradeepLakhina 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.
sql-server-2008sql-server-2005sqlsql-server-2008-r2sql-server-2012
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you clarify? Are you asking why you can't use a stored procedure inline with a select statement or a where/having clause?
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

PradeepLakhina avatar image
PradeepLakhina answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Jeff Moden avatar image
Jeff Moden answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
0 Likes 0 ·
srutzky avatar image
srutzky answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.