Problem calling a Function - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Problem calling a Function

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2008-03-11 : 18:48:40

I have the next Function:

CREATE FUNCTION F_StoreName
(
@strTienda as varchar(3) --Numero de Tienda
)
RETURNS VARCHAR(200)
AS

BEGIN

declare
@strStoreName as varchar(30),
@strSQLString nvarchar(500),
@strParmDefinition nvarchar(500)

--set @strTienda='003'

SET @strSQLString = N'select @StoreName = max(nombre) ' +
' from ' + master.dbo.fGetServerName('bdSupport') + 'bdSupport..tbTiendas with (noLock) ' +
' where notienda= ' + @strTienda

SET @strParmDefinition = N'@StoreName varchar(30) OUTPUT';
--print @strSQL
EXECUTE sp_executesql @strSQLString, @strParmDefinition, @StoreName=@strStoreName OUTPUT;

RETURN @strStoreName

END

When I call this function:

select dbo.F_StoreName('002') as x

It sent me next error:
Only functions and extended stored procedures can be executed from within a function.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-11 : 18:58:21

You cannot use dynamic SQL (including sp_executesql) within a function.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources