我当前的解决方案是创建一个包装器存储过程,该过程执行现有的包装器过程,并传递所有参数,但定义结果集的元数据。
继续问题中的示例:
EXEC sp_rename 'dbo.Test', 'Test_Logic', 'OBJECT';
GO
CREATE PROCEDURE dbo.Test
@Seed INT = 0
AS
BEGIN
EXEC dbo.Test_Logic
@Seed = @Seed
WITH RESULT SETS (
(
ID INT
)
)
;
END
EXEC sys.sp_describe_first_result_set @tsql = N'EXEC dbo.Test @Seed = 1;';
结果是:
is_hidden column_ordinal name is_nullable system_type_id system_type_name max_length precision scale collation_name user_type_id user_type_database user_type_schema user_type_name assembly_qualified_type_name xml_collection_id xml_collection_database xml_collection_schema xml_collection_name is_xml_document is_case_sensitive is_fixed_length_clr_type source_server source_database source_schema source_table source_column is_identity_column is_part_of_unique_key is_updateable is_computed_column is_sparse_column_set ordinal_in_order_by_list order_by_is_descending order_by_list_length tds_type_id tds_length tds_collation_id tds_collation_sort_id
--------- -------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- --------- ----- -------------------------------------------------------------------------------------------------------------------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------- ----------------- ------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ --------------------- ------------- ------------------ -------------------- ------------------------ ---------------------- -------------------- ----------- ----------- ---------------- ---------------------
0 1 ID 1 56 int 4 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 38 4 NULL NULL
(在这里看起来很糟糕,但是有效)
缺点是,现在我必须维护两个SP,并且需要执行后续的SP。
一种替代解决方案是使用动态sql并sys.sp_executesql
与该WITH RESULT SETS
子句一起运行它。
这样做的主要缺点是,sql Server和VisualStudio(数据库项目)无法跟踪动态sql中的依赖关系。
看起来像这样:
ALTER PROCEDURE dbo.Test
@Seed INT = 0
AS
BEGIN
CREATE TABLE #MyTemp (
ID INT NOT NULL
);
INSERT INTO #MyTemp (ID)
VALUES
(@Seed + 1)
, (@Seed + 2)
, (@Seed + 3)
;
DECLARE @STMT NVARCHAR(MAX) = N'
SELECT
ID
FROM
#MyTemp
;';
EXEC sys.sp_executesql
@stmt = @STMT
WITH RESULT SETS (
(
ID INT
)
)
END