使用函数返回值参数ADO.NET调用存储过程

I have this UDF that takes some parameters collected from an GUI.

我有这个UDF,它从GUI收集一些参数。

CREATE FUNCTION [dbo].[fnCreateAppoitmentXML] (@PacientID int
, @DataProgramare datetime2(7)
, @PacientNume varchar(50)
, @PacientEmail varchar(100)
, @PacientTelefon varchar(50)
, @PacientSimptome varchar(100))
RETURNS xml

AS
BEGIN


  RETURN (SELECT (SELECT
      @PacientId AS PacientId,
      @DataProgramare AS DataProgramare,
      @PacientNume AS PacientNume,
      @PacientEmail AS PacientEmail,
      @PacientTelefon AS PacientTelefon,
      @PacientSimptome AS PacientSimptome
    FOR
    xml PATH ('PacientDate'),
    TYPE)
  FOR xml PATH (''),
  ROOT ('root'))
END

From MVC 5 project I need to call below SP that takes an XML as parameter(the return type of the function)

从MVC 5项目我需要在SP下面调用一个XML作为参数(函数的返回类型)

@xmldoc xml
AS
BEGIN
  BEGIN TRANSACTION
    BEGIN TRY
      INSERT INTO [dbo].[ProgramareMF] (PacientID, DataProgramare, PacientNume, PacientEmail, PacientTelefon, PacientSimptome)
        (SELECT
          xmldoc.i.value('./PacientId[1]', 'int'),
          xmldoc.i.value('./DataProgramare[1]', 'datetime2(7)'),
          xmldoc.i.value('./PacientNume[1]', 'varchar(100)'),
          xmldoc.i.value('./PacientEmail[1]', 'varchar(100)'),
          xmldoc.i.value('./PacientTelefon[1]', 'varchar(100)'),
          xmldoc.i.value('./PacientSimptome[1]', 'varchar(100)')
        FROM @xmldoc.nodes('/root/PacientDate') AS xmldoc (i))
    COMMIT TRAN;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
      ROLLBACK TRAN;
    THROW;
  END CATCH;
END

My first question is can I ran a SP in .NET and supply it with UDF return value as a parameter. If this is possible should I store the return value of the function into a variable, or directly supply the SP with the UDF as a parameter???

我的第一个问题是我可以在.NET中运行SP并为其提供UDF返回值作为参数。如果这是可能的,我应该将函数的返回值存储到变量中,或者直接为SP提供UDF作为参数???

1 个解决方案

#1

You can't send the udf. It's sql, not c#.
But you can send the name of the udf and the parameters it should get and run it directly from the stored procedure. In this case, however, you can skip the udf all together and just send the parameters to the stored procedure.

你不能发送udf。这是sql,而不是c#。但是您可以发送udf的名称及其应该获取的参数,并直接从存储过程运行它。但是,在这种情况下,您可以一起跳过udf并将参数发送到存储过程。