sql server programming: Using SQL Server 2005, I'm trying to convert the following Stored
Procedure to a Function. Is this allowable (Exec string) in a Function.

TIA


Stored Procedure that works

CREATE PROCEDURE [dbo].[ExecuteMyTableFormula]
(
@appid varchar(50),
@parm varchar(2500)
)
AS
declare @cmd varchar(3000)
set @cmd = 'Select ' + @parm + ' as theResult from MyTable where AppId
= ' + @appid
exec (@cmd)

____________________________________________________________________


My attempt at converting it to a Function which doesn't like the Exec
statement


CREATE FUNCTION [CalcMyTableFormula] (@appid varchar(50), @parm
varchar(2500))
RETURNS float
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @theResult float
declare @cmd varchar(3000)
set @cmd = 'Select ' + @parm + ' as theResult from MyTable where AppId
= ' + @appid
exec @theResult = @cmd

RETURN(@theResult)
END;
GO

SalP (sal_paradise_93@yahoo.com) writes:
[quoted text, click to view]

You cannot use dynamic SQL in a function.

[quoted text, click to view]

See http://www.sommarskog.se/dynamic_sql.html#Dyn_update for a
better solution using static SQL.

However, I would not recommend using scalar UDF:s with data access,
at least not if you plan to use it from another query and call the
function for every row. That is expensive.

Also read this article to learn why you should use sp_executesql and
not EXEC() when using dynamic SQL.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Can you use sp_executesql in a Function?

SalP (sal_paradise_93@yahoo.com) writes:
[quoted text, click to view]

No. You cannot use dynamic SQL in a function. Period.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at