September 14, 2009 at 12:53 am
hi,
is there any specific reason that why cant we use a stored procedure in a functin
🙂
September 14, 2009 at 1:39 am
BOL States (http://msdn.microsoft.com/en-us/library/aa175085(SQL.80).aspx)
Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.
If you were allowed to call a stored procedure then you could do that.
I *think* that data changes are prevented as udfs are deterministic (http://msdn.microsoft.com/en-us/library/ms178091.aspx , ie they return the same data for the same inputs. If you could change the data then that wouldnt be true.
September 14, 2009 at 2:24 am
There's a way to call a Stored Procedure from a Function, but it's not very clean.
You can create a Linked Server pointing to the sql server itself and call it, for instance, 'SELF'.
Then you can run something like this:
use tempdb
go
create procedure sp_test
as
begin
select name
from sysobjects
end
go
create function fn_test()
returns table
as
return (
select *
from openquery(self, 'exec tempdb.dbo.sp_test')
)
go
select *
from dbo.fn_test()
It's a horrible hack, but it works.
-- Gianluca Sartori
September 14, 2009 at 2:37 am
Looking for spcefic reason not alternate solution
September 14, 2009 at 2:41 am
srikant maurya (9/14/2009)
Looking for spcefic reason not alternate solution
Maybe so, but i liked Gianluca Sartori's post. and the previous post gave a pretty good explanation.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 14, 2009 at 2:59 am
srikant maurya (9/14/2009)
Looking for spcefic reason not alternate solution
Probably you're not looking for advice either, but let me give you some: don't be so abrupt when replying to those who are willing to help you, or they will stop being.
Just a suggestion.
-- Gianluca Sartori
September 14, 2009 at 3:36 am
user defined functions are a limited, more specialized form of stored procedure. as a result, you don't have a full set of features. as specialized functions, they can return some specialized things like table variables.
Anything you can do with a function, you could do with a stored procedure; even the table variable can be replaced with a temp table and exec command.
here's a recap of User Defined Function Limitations:
··UDF has No Access to Structural and Permanent Tables.
··UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)
··UDF Accepts Lesser Numbers of Input Parameters.
··UDF can have upto 1023 input parameters, Stored Procedure can have upto 2100 input parameters.
··UDF Prohibit Usage of Non-Deterministic Built-in Functions Functions GETDATE() etc can not be used UDFs, but can be used in Stored Procedure
··UDF Returns Only One Result Set or Output Parameter Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure
··UDF can not Call Stored Procedure Only access to Extended Stored Procedure.
··UDF can not Execute Dynamic SQL or Temporary Tables
··UDF can not run dynamic SQL which are dynamically build in UDF. Temporary Tables can not be used in UDF as well.
··UDF can not Return XML FOR XML is not allowed in UDF
··UDF does not support SET options SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
··UDF does not Support Error Handling RAISEERROR or @@ERROR are not allowed in UDFs.
··UDF does not Support print statements for debugging
Lowell
September 14, 2009 at 4:23 am
Functions are prevented from calling a stored procedure directly as part of a package of protections built in to SQL Server.
These protections guarantee that the state of the database does not change during function execution.
(By changing state, I mean that operations like changing data or creating tables are absolutely forbidden.)
If we were able to change database state inside a function, SQL Server would no longer be able to guarantee the transactional integrity of the system. This is absolutely by design. Functions must be entirely self-contained, as far as database state is concerned. SQL Server doesn't care whether the function is deterministic or not, it just cares about database state.
An example may help:
Consider a simple scalar function (dbo.fn_Double) that returns INTEGER output 2x for any SMALLINT input x.
Let's say we call that function as part of a statement like: UPDATE dbo.Table SET col = dbo.fn_Double(data);
That's fine.
Now let us allow the function to change database state internally.
We change the function to let it set 'data' to NULL in dbo.Table.
I hope you will agree that the results could well be horrific.
A function is a very different creature from a procedure, though they appear similar to many people. Essentially a function is more akin to a mathematical function than a programming language function.
A mathematical function may only read its input and return a value - it does not change the input or affect other variables in the same equation. That is the intentional nature of SQL Server functions.
(In-line and table-valued functions are just a natural set-based extension of the same scalar function idea.)
Try changing Gianluca's example with the loopback linked server to include a CREATE TABLE or DML statement (like an INSERT) in the called procedure. It will compile, run, and be completely ignored by SQL Server! The rest of the procedure will run completely and successfully, but you cannot and must not change database state.
I should mention that Microsoft do allow this rule to be broken for CLR functions - but only if you mark the assembly as UNSAFE, and use an external database connection to change database state. Attempts to change state on the context connection will fail. I cannot stress strongly enough how dumb it would be to attempt to get around the rules using a CLR function - you will get hurt!
The protections are there for sound reasons. Do not attempt to make a function do more than a mathematical function with read-only data access can do. If you think you need to do something with a function that requires breaking the rules, you are doing it wrong, and need to rethink.
There are a couple of side-effects which are understandable, but unfortunate. One is the restriction on not being able to call dynamic SQL. Many people regard this as unreasonable, since SQL Server can always validate the code at execution time. The problem is one of practicality. A silly person could write a truly horrible recursive function this way - and the complexity of implementing the necessary checks in SQL Server is such as to make it safer and simpler for everyone to just disallow it.
I hope this goes some way toward answering your question.
Paul
code to amend Gianluca's example:
alter procedure sp_test
as
begin
-- Ignored - no error
create table dbo.fish (a int)
-- Succeeds and returns rows
select name
from sysobjects
end
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 4:34 am
Really interesting one, Paul.
I didn't know that the engine ignores the changes to the db state.
I'm just wondering why you seem to know the topic so deeply: maybe you got stuck trying to work around it yourself?
-- Gianluca Sartori
September 14, 2009 at 4:49 am
Gianluca Sartori (9/14/2009)
Really interesting one, Paul.I didn't know that the engine ignores the changes to the db state.
I'm just wondering why you seem to know the topic so deeply: maybe you got stuck trying to work around it yourself?
Please try it out for yourself - most people find it very surprising 🙂
Thanks for posting the linked server example - it prompted me to stop sighing and actually reply! Very neat.
And yes, you are right. When functions first arrived, I tried to do dumb stuff with them too. A number of discussions with a number of people (that I respect highly) over the years, together with a good deal of experimentation, soon set me straight.
Developers (bless them) at work often mistake SQL Server functions for C#, and also try to do dumb stuff.
I don't blame them really, calling them functions and allowing .NET implementations was probably asking for trouble...:-D
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 5:25 am
Just to follow the Moden Rules, here's a demo of a truly non-deterministic function:
CREATE FUNCTION dbo.fn_test2()
RETURNS BINARY(8)
WITH SCHEMABINDING
AS
BEGIN
RETURN CONVERT(BINARY(8),@@DBTS);
END;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 6:30 am
I stand corrected 😉
Just to further clarify what Paul said....
drop View VwNewCheck
go
Create View VwNewCheck
with schemabinding
as
Select abs(checksum(NewId())) as New_Id
go
Drop FUNCTION dbo.fn_test2
go
CREATE FUNCTION dbo.fn_test2()
RETURNS table
WITH SCHEMABINDING
AS
return
select New_Id from dbo.VwNewCheck ;
go
select top 10 Object_id,new_id from sys.tables cross apply dbo.fn_test2()
go
September 15, 2009 at 9:12 am
dba-vb
When these sorts of puzzles come up, the first resource is SQL-Server Books-on-line.
A next authority is MSDN, the Microsoft Developer Network web pages.
(a) The following web site lists the statements that are allowed in a user defined fuction, and says that statements not in the list are not allowed.
http://msdn.microsoft.com/en-us/library/aa258261(SQL.80).aspx
The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:
- Assignment statements.
- Control-of-Flow statements.
- DECLARE statements defining data variables and cursors that are local to the function.
- SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
- Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
-INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
--===================================================
-EXECUTE statements calling an extended stored procedures.
--===================================================
Security Note Validate all user input. Do not concatenate user input before validating it. Never execute a command constructed from unvalidated user input. For more information, see Validating User Input.
(b) from the above we see that a user defined function can call an extended stored procedure. But what, precisely is that?
At the MSDN web page below we see that an extended stored procedure is packaged as a DLL and developed using a programming language that is not SQL.
http://msdn.microsoft.com/en-us/library/aa197263(SQL.80).aspx
An extended stored procedure is packaged as dynamic-link libraries (DLLs), extended stored procedures provide a way to extend SQL Server functionality through functions developed by using C/C++, the Open Data Services API, and the Microsoft Win32® API. These functions can send result sets and output parameters back to the client from a variety of external data sources.
If your question is asking for proof that a user defined function cannot call a stored procedure, this may be authoritative enough.
If your questions is asking 'Why' the decision was made by Microsoft to do it this way, then the earlier responses give answers to that.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply