Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Function with 2 parameter, 1 is fieldname

Author  Topic 

micnie_2020
Posting Yak Master

232 Posts

Posted - 2015-01-30 : 10:02:35
Hi All,

Why my function return error:-

Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function.


Alter function [dbo].[getInfo] (@AID int,@fieldDesc nvarchar(max))
--select dbo.getInfo(15,'Description')
RETURNS nvarchar(max)
as
BEGIN
declare @Output nvarchar(max)
DECLARE @sSQL nvarchar(max)
SET @sSQL=N'select @Output='+@fieldDesc+' from tabA where AID='''+cast(@AID as nvarchar(20))+''''
SET @sSQL=@sSQL + ' UNION ALL '
SET @sSQL='select @Output='+@fieldDesc+' from tabB where AID='''+cast(@AID as nvarchar(20))+''''
SET @sSQL=@sSQL + ' UNION ALL '
SET @sSQL='select @Output='+@fieldDesc+' from tabC where AID='''+cast(@AID as nvarchar(20))+''''
exec sp_executesql @sSQL
RETURN @Output
END


Please advise.

Thank you.

Regards,
Micheale

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-01-30 : 10:07:21
SQL Server does not allow dynamic SQL in user defined functions.
Go to Top of Page

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-30 : 10:22:33
You could achieve this method only in the Stored Procedures. You cannot execute SP inside the functions.

Regards
Viggneshwar A
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-02-02 : 01:13:38
Also make sure to read this fully www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -