Function with 2 parameter, 1 is fieldname

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.