September 26, 2011 at 9:59 am
Hello Friends,
We all know that; we can't use DDL in between a sql function
but what is the architectural reason behind it. I m not finding
it. Plz help me out.:hehe:
September 26, 2011 at 10:12 am
I think you mean DDL.
One main purpose of a function is to manipulate data and return the result. That can be done without altering structures. If you need to use some sort of DDL, you can mimic that via CTE.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 26, 2011 at 10:22 am
ohh.. sorry my mistake .. I mean DDL TOO...
Thank you Jason. But can you put more focus on it.
like wise some time we have to do Complex query such as gather data
from multiple tables and insert into any temp table than further manipulation.
we can't do like that here but the question WHY . what type of architecture
sql server follow to build and run the function so that we could not allow those DDL operations.
and one more doubt can we call Stored Procedure In it....
September 26, 2011 at 10:24 am
That's a choice made by the dev team at MS.
Maybe there's something in the sql standards as well.
Bottom line is.
That's how it is. Then end, move on, learn to deal with it.
September 26, 2011 at 10:25 am
No you can't call sp because by DESIGN the function CAN'T change the data.
What the heck are you trying to do that requires you to use functions to do????????
September 26, 2011 at 10:38 am
thank you friends ,
I were just seeking for architecture of UDF.
September 26, 2011 at 10:45 am
shivendrakumaryadav60 (9/26/2011)
thank you friends ,I were just seeking for architecture of UDF.
In programming languages, a function can do everything a Subroutine can do, plus return a value.
in SQL it is different. a function is a limited, specialized type of procedure. To maximize performance by preventing a function from affecting the structure of any objects, they amount of validating required for a function is faster
if you cannot CREATE a function that can break something, you don't have to check the command it runs to see if it might break something..
here's a set of rules for refernece, inc ase you want to review them again:
··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 26, 2011 at 10:55 pm
Thank you very much Lowell...
Its help me a lot.. thanks again... :Wow:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply