通过执行命令从另一个存储过程调用一个存储过程

Question

在我的存储过程中，我必须传递一个可能每次都会更改的表和列名称。所以我构建命令并执行它。我想要另一个变量@curr_id 中的输出。第二个存储过程将使用 @curr_id 作为输入来调用此存储过程。

我的问题是填充@curr_id vriable。它返回为零。如果我删除 curr_id 变量然后它就可以工作（参见注释行）

有人可以吗？告诉我 - 如何填充@curr_id并作为输出返回？ - 如何从第二个存储过程调用第一个存储过程？

ALTER PROCEDURE [dbo].[sp_astm_getcurrid]
@ColName as nvarchar(250),
@TblName as nvarchar(250),
@curr_id nvarchar(max) OUTPUT 
AS
BEGIN
DECLARE @cmd nvarchar(max)

SET @cmd =N'SET '+@curr_id+'= SELECT MAX('+@ColName+') FROM '+@TblName;
--SET @cmd =N'SELECT MAX('+@ColName+') FROM '+@TblName;

EXEC (@cmd)

END

Answer 1

由于 EXEC 在不同的上下文中运行，因此它不知道您的 @curr_id 变量。相反，您可以将动态 SQL 的输出放入表变量中，然后使用它来设置 @curr_id。

另外，永远不要用 sp_ 启动存储过程名称。

    ALTER PROCEDURE [dbo].[usp_astm_getcurrid]
        @ColName as nvarchar(250),
        @TblName as nvarchar(250),
        @curr_id nvarchar(max) OUTPUT 
    AS
    BEGIN
        DECLARE @cmd nvarchar(max)

        declare @dummy table (
            ReturnColumn nvarchar(max)
        )

        SET @cmd = N'SELECT MAX(' + @ColName + N') FROM ' + @TblName;

        insert into @dummy
            (ReturnColumn)
            exec (@cmd)

        set @curr_id = (select ReturnColumn from @dummy)
    END

然后，从另一个过程中调用此过程的示例可能如下所示。重要的关键是在调用以及上面过程的声明中使用 OUTPUT 关键字。

    CREATE PROCEDURE CallMyProcedure
    AS
    BEGIN
        declare @curr_id nvarchar(max)
        exec dbo.usp_astm_getcurrid N'YourColumnName', N'YourTableName', @curr_id OUTPUT
        select @curr_id
    END

Answer 2

Friend Function execSP(ByVal spName As String, Optional ByVal params As Collection = Nothing) As Integer
        Dim cmd As SqlCommand
        Dim param As SqlParameter
        Dim ret As Integer
        Dim iParam As Integer

        cmd = New SqlCommand
        cmd.CommandText = spName
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = _sqlConn
        cmd.CommandTimeout = 0

        If Not params Is Nothing Then
            For iParam = 1 To params.Count
                param = params(iParam)
                cmd.Parameters.Add(param)
            Next
        End If

        If _sqlConn.State <> ConnectionState.Open Then
            _sqlConn.Open()
        End If

        Try
            ret = cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            _sqlConn.Close()
        End Try
        Return ret
    End Function

Answer 3

您可以在 sp_executesqlOUTPUT 参数>：

ALTER PROCEDURE [dbo].[sp_astm_getcurrid] 
@ColName as nvarchar(250), 
@TblName as nvarchar(250), 
@curr_id nvarchar(max) OUTPUT  
AS 
BEGIN 

DECLARE 
  @cmd nvarchar(max)

SET @cmd =N'SELECT @curr_id_out = MAX('+@ColName+') FROM '+@TblName 

EXEC sp_executesql 
  @cmd, 
  N'@curr_id_out nvarchar(max) OUTPUT', 
  @curr_id_out = @curr_id OUTPUT

END 

如果不提一下，我就太失职了，在这种情况下，使用 EXEC(@cmd) 或 sp_executesql(@cmd) 会让您容易受到 SQL 注入 攻击。

我建议在存储过程的开头添加类似以下内容：

SELECT 
  @ColName = REPLACE(
             REPLACE(
             REPLACE(@ColName, ';', ''), '-', ''), '''', ''),
  @TblName = REPLACE(
             REPLACE(
             REPLACE(@TblName, ';', ''), '-', ''), '''', '')