How to call a stored procedure from a user defined function In SQL 2000

Question

How to call a stored procedure from a user defined function In SQL 2000

Answer 1

You can't call regular stored procs from functions - only other functions or some extended stored procedures. See here for the BOL article (from SQL 2005). Attempting to call a standard stored proc from a UDF will result in the following error...

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

Answer 2

Hi

Either you need to modify your stored procedure to be a user defined function or the other way around.

One crude way to achieve what you are looking for is to have your exec statement in a batch script and call that batch script from your function. Something like this -

create function <functionName>
exec master.sys.xp_cmpshell 'C:\storedProc.bat'
....
....

return @return
end

More on xp_cmpshell - http://msdn.microsoft.com/en-us/library/aa260689(SQL.80).aspx

I hope this helps.

cheers

Answer 3

Hello everybody,

I recently had a similar issue. In fact the error message is not properly formatted since sp_executesql is an extended stored procedure as you can check by the following script: select objectproperty(object_id('sp_executesql'),'IsExtendedProc')

returns

1

Since we can’t use sp_executesql even it’s a XP, I had to found another workaround by using sp_OAMethod: My scenario was: how to find dynamically the number of rows in a table according some criteria (null values in my scenario). Using sp_OAMethod I built the following function:

IF object_id(N'dbo.fc_ContaRegistros_x_Criterio') is not null DROP FUNCTION [dbo].[fc_ContaRegistros_x_Criterio] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE FUNCTION dbo.fc_ContaRegistros_x_Criterio ( @str_TBName VARCHAR(100), @str_Criter VARCHAR(500)) RETURNS BIGINT AS BEGIN -- Objetivo : Contar numero de registros de uma determinada tabela de acordo com o critério passado -- Criação : Josué Monteiro Viana - 09/07/09 /* Exemplo: DECLARE @count INT SET @count = dbo.fc_ContaRegistros_x_Criterio('master.dbo.sysobjects', '') PRINT @count SET @count = dbo.fc_ContaRegistros_x_Criterio('crk.dbo.acao', 'where cod_acao is null') PRINT @count */ DECLARE @int_objSQL INT, @int_erros INT, @int_objSelectCountResult INT, @bint_SelectCount BIGINT, @sql NVARCHAR(2000)

EXEC @int_erros = sp_OACreate 'SQLDMO.SQLServer', @int_objSQL OUTPUT
EXEC @int_erros = sp_OASetProperty @int_objSQL, 'LoginSecure', TRUE
EXEC @int_erros = sp_OAMethod @int_objSQL, 'Connect', null, '.'
--SET @sql = 'SELECT count(*) FROM ' + @str_TBName + ' WHERE ' + @str_Criter 
SET @sql = 'SELECT count(*) FROM ' + @str_TBName + ' ' + @str_Criter 
SET @sql = 'ExecuteWithResults("' + @sql + '")'
EXEC @int_erros = sp_OAMethod @int_objSQL, @sql, @int_objSelectCountResult OUTPUT
EXEC @int_erros = sp_OAMethod @int_objSelectCountResult, 'GetRangeString(1, 1)', @bint_SelectCount OUT
EXEC @int_erros = sp_OADestroy @int_objSQL
-- debug info: not valid inside a fc
--if @int_erros <> 0 EXEC sp_OAGetErrorInfo @int_objSQL else print 'ok'
if @int_erros <> 0 SET @bint_SelectCount = @int_erros
RETURN @bint_SelectCount

END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON

GO

I know your case is a little different, but I’m sure you can use this udf as a guideline to help you.

Best wishes, Josue Monteiro Viana