how to use a store procedure into a function ? what is it wrong in my code?

Not sure what your exact scenario is, but there are several restrictions on implementing a function within MS SQL Server. It is not like a function in a programming language.

For example, you should not change the state of the database, etc. As such executing a sp with function might be prohibited as it cannot know what a sp is doing in turn.

Please go through all restrictions at following link:

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine

Pls try this -

You have to insert data in a temp variable and then you assign that to a variable and then return. I have changed code for you.

create function dbo.LeFormula(@codpro varchar(24), 
                              @codespec varchar(5),
							  @formula varchar(500), 
							  @CodcampoFormula varchar(100)) 
returns varchar(100)
as
begin 
	declare @valor1 as varchar(100)

	DECLARE @t TABLE(valor varchar(100))	 
  
    INSERT INTO @t(valor)
	Exec sp_CALCULO_FORMULA_ESPEC @codpro, @codespec, '', '', '', '', '', @formula, @CodcampoFormula 

	SELECT @valor1 = valor FROM @t

   return @valor1

End

Select allOpen in new window

I got this Pawan Kumar

Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

Sorry my bad , you cannot call a SP inside a function.

you can embed you sp code inside the function as a solution.

Please see if this works for you-

create function dbo.LeFormula(@codpro varchar(24), 
                              @codespec varchar(5),
							  @formula varchar(500), 
							  @CodcampoFormula varchar(100)) 
returns varchar(100)
as
begin 
	declare @valor1 as varchar(100)

	DECLARE @t TABLE(valor varchar(100))	 
  
    INSERT INTO @t(valor)
	SELECT * from OPENROWSET('SQLNCLI10', 'Server=<SERVERNAME>;UID=<LOGIN>;Pwd=<PASSWORD>;','Exec sp_CALCULO_FORMULA_ESPEC @codpro, @codespec, '''', '''', '''', '''', '''', @formula, @CodcampoFormula') AS C 

	SELECT @valor1 = valor FROM @t

   return @valor1

End

Select allOpen in new window

is it possible only with OPENROWSET ?

I have not tried with OpenRowSet.  I think i may work.
You can try.

the problem is the password, login name inside de code.

All my problem is because I need to get the value from my store proc and retrieve it in my query, to solve it I thought to call it through a function.

You can use a stored procedure.

In my query? How?

I have tried this but it doesn't allow it

SELECT
    Relat_PCP.NUMPED, Relat_PCP.SEQPED, Relat_PCP.DATPED, Relat_PCP.OBS, Relat_PCP.NOMCAD, Relat_PCP.NOMFAN, Relat_PCP.DATPREV_FINALIZACAO,
    Relat_PCP_Itens.CODPRO, Relat_PCP_Itens.NOMPRO, Relat_PCP_Itens.QTDPROPROD, Relat_PCP_Itens.LOTE, Relat_PCP_Itens.NOMTIPPRO, Relat_PCP_Itens.NOMAPLICA,
    (select NOMFAM from arest A INNER JOIN ArFAM B ON B.CODFAM=A.CODFAM where A.Codpro=Relat_PCP_Itens.CODPRO),
    (select dbo.fn_PegaDados_EspTec_Arest_Crystal(Relat_PCP_Itens.CODPRO, isnull(Relat_PCP_Itens.LOTE,'') , 'LARGURA1')),
    (select dbo.fn_PegaDados_EspTec_Arest_Crystal(Relat_PCP_Itens.CODPRO, isnull(Relat_PCP_Itens.LOTE,'') , 'MEIO')),
    (select dbo.fn_PegaDados_EspTec_Arest_Crystal(Relat_PCP_Itens.CODPRO, isnull(Relat_PCP_Itens.LOTE,'') , 'LARGURA2')),
    (select dbo.fn_PegaDados_EspTec_Arest_Crystal(Relat_PCP_Itens.CODPRO, isnull(Relat_PCP_Itens.LOTE,'') , 'ESPESSURA')),
	(Exec sp_CALCULO_FORMULA_ESPEC '000990','001', '', '', '', '', '', '((MEIO@)*(LARGURACHAPA@)*(ESPESSURA@)*(PESPECIFICO@)*(OBSIMPRESSAO@))', 'KG')
FROM
    Relat_PCP Relat_PCP,
    Relat_PCP_Itens Relat_PCP_Itens
WHERE
    Relat_PCP.NUMPED = Relat_PCP_Itens.NUMPED AND
    Relat_PCP.SEQPED = Relat_PCP_Itens.SEQPED AND
    Relat_PCP.NUMPED = 4410 AND
    Relat_PCP.SEQPED = 0

Select allOpen in new window

Please try this - this should work.

DECLARE @t TABLE(valor varchar(100))	 
INSERT INTO @t(valor)
Exec sp_CALCULO_FORMULA_ESPEC '000990','001', '', '', '', '', '', '((MEIO@)*(LARGURACHAPA@)*(ESPESSURA@)*(PESPECIFICO@)*(OBSIMPRESSAO@))', 'KG'

SELECT
    Relat_PCP.NUMPED, Relat_PCP.SEQPED, Relat_PCP.DATPED, Relat_PCP.OBS, Relat_PCP.NOMCAD, Relat_PCP.NOMFAN, Relat_PCP.DATPREV_FINALIZACAO,
    Relat_PCP_Itens.CODPRO, Relat_PCP_Itens.NOMPRO, Relat_PCP_Itens.QTDPROPROD, Relat_PCP_Itens.LOTE, Relat_PCP_Itens.NOMTIPPRO, Relat_PCP_Itens.NOMAPLICA,
    (select NOMFAM from arest A INNER JOIN ArFAM B ON B.CODFAM=A.CODFAM where A.Codpro=Relat_PCP_Itens.CODPRO),
    (select dbo.fn_PegaDados_EspTec_Arest_Crystal(Relat_PCP_Itens.CODPRO, isnull(Relat_PCP_Itens.LOTE,'') , 'LARGURA1')),
    (select dbo.fn_PegaDados_EspTec_Arest_Crystal(Relat_PCP_Itens.CODPRO, isnull(Relat_PCP_Itens.LOTE,'') , 'MEIO')),
    (select dbo.fn_PegaDados_EspTec_Arest_Crystal(Relat_PCP_Itens.CODPRO, isnull(Relat_PCP_Itens.LOTE,'') , 'LARGURA2')),
    (select dbo.fn_PegaDados_EspTec_Arest_Crystal(Relat_PCP_Itens.CODPRO, isnull(Relat_PCP_Itens.LOTE,'') , 'ESPESSURA')),
	(SELECT valor FROM @t)
FROM
    Relat_PCP Relat_PCP,
    Relat_PCP_Itens Relat_PCP_Itens
WHERE
    Relat_PCP.NUMPED = Relat_PCP_Itens.NUMPED AND
    Relat_PCP.SEQPED = Relat_PCP_Itens.SEQPED AND
    Relat_PCP.NUMPED = 4410 AND
    Relat_PCP.SEQPED = 0

Select allOpen in new window

This way as you told me, works fine.
But I can't use this way because this query is used in my crystal reports version 8.5
I am also trying to find another way,

is it possible to create a view with that code?

Yeah, you gave me a good ideia. I'll do that

thanks