主页 / dba / 问题 / 13782
Accepted
adopilot
Asked: 2012-02-24 08:34:06 +0800 CST

表值参数作为存储过程的输出参数

  • 772

表值参数是否可以用作存储过程的输出参数？

这是我想在代码中做的事情

/*First I create MY type */
CREATE TYPE typ_test AS TABLE 
(
     id int not null
    ,name varchar(50) not null
    ,value varchar(50) not null
    PRIMARY KEY (id)
)
GO


--Now I want to create stored procedu whic is going to send output type I created, 
--But it looks like it is inpossible, at least in SQL2008
create  PROCEDURE [dbo].sp_test
         @od datetime 
        ,@do datetime 
        ,@poruka varchar(Max) output
        ,@iznos money output 
        ,@racun_stavke  dbo.typ_test   READONLY --Can I Change READONLY with OUTPUT ?
AS
BEGIN
    SET NOCOUNT ON;

    /*FILL MY OUTPUT PARAMS AS I LIKE */


    end
sql-server-2008 stored-procedures

4 个回答

  1. Best Answer

    不，不幸的是，表值参数是只读的并且只能输入。总体而言，如何在存储过程之间共享数据中很好地涵盖了该主题，其中介绍了所有替代方案。我的建议是使用一张#temp桌子。

    • 43

  2. 这是一篇较旧的帖子，但当我搜索“表值参数作为存储过程的输出参数”时，它接近顶部。虽然我的理解是您不能将表值参数作为输出参数传递，但我想目标是在另一个过程中将该表值输出参数用作表值输入参数。我将举例说明我是如何完成这项工作的。

    首先，创建一些要使用的数据：

    create table tbl1
(
id int,
fname varchar(10),
gender varchar(10)
);
create table tbl2
(
id int,
lname varchar(10)
);
insert into tbl1
values
(1,'bob'  ,'m'),
(2,'tom'  ,'m'),
(3,'sally','f')
;
insert into tbl2
values
(1,'jones'   ),
(2,'johnson' ),
(3,'smith'   )
;

    接下来，创建一个存储过程来捕获一些数据。通常，这将是您尝试创建表值输出参数的地方。

    create procedure usp_OUTPUT1
 @gender varchar(10)
as
Begin
    select id from tbl1 where gender = @gender
End

    此外，您需要创建一个数据类型（表类型），其中第一个存储过程中的数据可以作为下一个存储过程的输入参数传递。

    create type tblType as Table (id int)

    接下来，创建将接受表值参数的第二个存储过程。

    create procedure usp_OUTPUT2
@tblType tblType readonly  --referencing the type created and specify readonly
as
begin
 select lname from tbl2 where id in (select id from @tblType)
end

    当然，这不是一个真正的表值输出参数，但它可能会产生与您要寻找的结果相似的结果。声明表值参数，通过在其中执行存储过程来填充数据，然后将其用作下一个过程的输入变量。 

    Declare @tblType tblType 
insert into @tblType execute usp_OUTPUT1 'm'
execute usp_OUTPUT2 @tblType
    • 2

  3. 除了remus 的精彩回答，包括他提供的链接

    如何在存储过程之间共享数据

    在将存储过程的结果保存到表中时，有时会收到以下错误消息：

    INSERT EXEC 语句不能嵌套。

    当前事务无法提交，也无法支持写入日志文件的操作。回滚事务

    当这种情况发生在我为自己使用而开发的存储过程中时

    例如，一个工具可以告诉我login它所属的所有 AD 组及其在服务器中所有数据库中的所有权限

    我在过程之外创建一个临时表并将其名称作为参数传递

    --===============
-- this way below it works, by passing a temp table as a parameter
--===============

                if OBJECT_ID('tempdb.dbo.#my_table') IS NOT NULL
                   DROP TABLE #my_table

                CREATE TABLE #my_table(
                    db nvarchar(128)   COLLATE Latin1_General_CI_AS  NULL,
                   permission_type nvarchar(128)   COLLATE Latin1_General_CI_AS  NULL,
                    login_  nvarchar(128)   COLLATE Latin1_General_CI_AS  NULL,
                    role_  nvarchar(128)   COLLATE Latin1_General_CI_AS  NULL,
                    Obj    nvarchar(517)   COLLATE Latin1_General_CI_AS  NULL,
                    Permission nvarchar(128)   COLLATE Latin1_General_CI_AS  NULL,
                    script nvarchar(1008)  COLLATE Latin1_General_CI_AS  NULL
                ) 

                exec sp_GetLoginDBPermissionsX 
                    @Login='my_loginname', 
                    @debug=0,
                    @where_to_save ='#my_table'

                select *
                from #my_table

    在程序内部，经过所有计算，当我返回最终数据时（下面是一个示例），我检查我们是输出到表格还是只是返回到屏幕并动态创建脚本。

                select @sql = case when @where_to_save IS not null then 
            '
            insert into ' + @where_to_save + '(db,Permission_Type,login_,role_,obj,Permission,script) '
            else '' end + 
'
        SELECT 
            J.db,
            J.Permission_Type,
            J.login_,
            J.role_,
            J.Obj,
            J.Permission,
            J.script
        FROM #tablewithpermissions J
        WHERE J.login_ IN ( SELECT  L1.LOGIN_FROM COLLATE Latin1_General_CI_AS FROM #logins L1)
           OR J.role_ IN  ( SELECT  L1.LOGIN_FROM COLLATE Latin1_General_CI_AS FROM #logins L1)
       ORDER BY J.DB, J.[permission_order]
'
        --print(@sql)

        EXEC(@SQL)

    之后，您可以在屏幕上获得所需的信息，或者如果您已将临时表作为参数传递，它现在将拥有数据。

    这是我找到的一种解决方案，但我只将它用于我自己的工作，DBA否则这将被视为Sql Injection的高风险。

    • 2

  4. 可以将表转换为 JSON（SQL Server 2016 或更高版本）并作为 nvarchar 参数传递：

    CREATE PROCEDURE test
  @json nvarchar(max) output
AS
BEGIN
  SET NOCOUNT ON;

  --Create table variable
  declare @t1 as table ([value] nvarchar(max), [ordinal] int);

  --Fill table
  INSERT INTO @t1 ([value], [ordinal])
  SELECT 'abc', 1 UNION
  SELECT 'def', 2;

  --Convert table to JSON
  set @json  = (SELECT * FROM @t1 FOR JSON PATH);
END

    [{"value":"abc","ordinal":1},{"value":"def","ordinal":2}]

    然后要将其转换回表，请将列名指定为 JSON 键：

    declare @json nvarchar(max);

exec test @json output;

--Convert JSON to table
SELECT * FROM OPENJSON(@json)
WITH ([value] nvarchar(max) '$.value', [ordinal] int '$.ordinal');
    • 0

相关问题