I have to create a stored procedure that receives one parameter and returns another. Within the procedure I declare a variable and give it value with a select. Later with the input variable and the one obtained from the select I invoke a dll. I have this, but the SP is not created:
CREATE PROCEDURE [dbo].[SpInvoke]
(@funcion NVARCHAR (MAX),
@salida REAL OUTPUT)
AS
declare @url as nvarchar (max) = SELECT TOP 1 url FROM Config
EXTERNAL NAME [User.BBDD.SQLServer].[StoredProcedures].[SpInvoke]
GO
According to the documentation the only line of code that is allowed with an SP that calls a dll is EXTERNAL, so you can not use another SQL statement inside.
I recommend you do the following. The variable url initializes it with the value of the query outside the SP and then sends it as a parameter. This way you can call the dll with that value. Something like this:
CREATE PROCEDURE [dbo].[SpInvoke]
(@funcion NVARCHAR (MAX),
@url nvarchar (max),
@salida REAL OUTPUT)
AS
EXTERNAL NAME [User.BBDD.SQLServer].[StoredProcedures].[SpInvoke]
GO
/*Antes de llamar al SP*/
declare @url as nvarchar (max) = (SELECT TOP 1 url FROM Config)
Excute PROCEDURE [dbo].[SpInvoke] @funcion, @url,...