CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sql
HDB
HDB

Reputation: 301

Dynamic SQL from a Dynamic View to Return the field value of a record

this function should accept the viewname and return the date on the last record of the view. Can someone tell me what am I missing here?

I call the function in a stored procedure and got this error:

Only functions and some extended stored procedures can be executed from within a function.

My query:

ALTER FUNCTION [dbo].[udf_GetLastDate] (@ViewName nvarchar(4000))
RETURNS date
AS
BEGIN
   DECLARE @SQLCommand nvarchar(4000);
   DECLARE @LastTransDate date; 
   SET @SQLCommand = 'SELECT @LastTransDate=LAST(TRANSDATE) FROM' + @ViewName;
   EXECUTE sp_executesql @sqlCommand
   RETURN @LastTransDate;
end

Upvotes: 0

Views: 230

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176284

The problem is you are trying to execute Dynamic-SQL from function. You simply cannot do it. Period.

Dynamic SQL in User-Defined Functions:

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.

I've seen more than one post on the newsgroups where people have been banging their head against this. But if you want to use dynamic SQL in a UDF, back out and redo your design. You have hit a roadblock, and in SQL 2000 there is no way out.

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. If you say

SELECT ... FROM tbl WHERE dbo.MyUdf(somecol) = @value

and MyUdf performs data access, you have more or less created a hidden cursor.

Consider changing function to stored procedure:

CREATE PROCEDURE [dbo].[mysp_GetLastDate]
  @ViewName SYSNAME
AS
BEGIN
   DECLARE @SQLCommand NVARCHAR(MAX) = 
        N'SELECT MAX(TRANSDATE) FROM' + QUOTENAME(@ViewName);

   EXECUTE [dbo].[sp_executesql] 
             @sqlCommand;
END

Also keep in mind that SQL Server does not have LAST function. If you need newest TRANSDATE use MAX() or SELECT TOP 1 TRANSADATE FROM ... ORDER BY some_column DESC

Upvotes: 2

Related Questions