Call dynamic SQL from function

by

You can’t use dynamic sql in a udf:

This very simple: you cannot use dynamic SQL from used-defined
functions written in T-SQL. This is because you are not permitted do
anything in a UDF that could change the database state (as the UDF may
be invoked as part of a query). Since you can do anything from dynamic
SQL, including updates, it is obvious why dynamic SQL is not
permitted.

…

In SQL 2005 and later, you could implement your function as a CLR
function. Recall that all data access from the CLR is dynamic SQL.
(You are safe-guarded, so that if you perform an update operation from
your function, you will get caught.) A word of warning though: data
access from scalar UDFs can often give performance problems.

More Related Contents:

  1. T-SQL dynamic pivot
  2. Calculate a Running Total in SQL Server
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. Not equal != operator on NULL
  5. SET NOCOUNT ON usage
  6. How can I get column names from a table in SQL Server?
  7. How can I select the first day of a month in SQL?
  8. Subquery using Exists 1 or Exists *
  9. Most efficient T-SQL way to pad a varchar on the left to a certain length?
  10. Is it possible to specify condition in Count()?
  11. Update records in table from CTE
  12. how can I Update top 100 records in sql server
  13. @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
  14. What is the difference between function and procedure in PL/SQL?
  15. How can a LEFT OUTER JOIN return more records than exist in the left table?
  16. Count Number of Consecutive Occurrence of values in Table
  17. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  18. Pivot data in T-SQL
  19. How to calculate the number of “Tuesdays” between two dates in TSQL?
  20. How to split string using delimiter char using T-SQL?
  21. Get the records of last month in SQL server
  22. Merge overlapping date intervals
  23. I want to use CASE statement to update some records in sql server 2005
  24. Access to Result sets from within Stored procedures Transact-SQL SQL Server
  25. alternatives to REPLACE on a text or ntext datatype
  26. How to return a incremental group number per group in SQL
  27. SQL Server SELECT INTO @variable?
  28. Select a column if other column is null
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment