查询存储过程的参数信息的SQL语句[经过测试]

最新推荐文章于 2023-06-15 23:52:01 发布

weixin_34185364

最新推荐文章于 2023-06-15 23:52:01 发布

阅读量219

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/kkun/archive/2009/02/05/1384381.html

本文提供了一段SQL脚本，用于查询指定存储过程的所有参数详情。包括参数名称、位置、类型等属性，并展示了如何设置及获取存储过程的返回值。

测试环境:SQL SERVER 2005

SQL语句

Code
declare @SchemaName nvarchar(50)
set @SchemaName='构架名称,如DBO'
declare @CommandName nvarchar(50)
set @CommandName='存储过程名称'

SELECT
                DB_NAME() AS [PROCEDURE_CATALOG],
                @SchemaName AS [PROCEDURE_SCHEMA],
                NULL AS [PROCEDURE_NAME],
                '@RETURN_VALUE' AS [PARAMETER_NAME],
                0 AS [ORDINAL_POSITION],
                CAST(4 AS smallint) AS [PARAMETER_TYPE],
                0 AS [PARAMETER_HASDEFAULT],
                NULL AS [PARAMETER_DEFAULT],
                CAST(0 AS bit) AS [IS_NULLABLE],
                0 AS [DATA_TYPE],
                NULL AS [CHARACTER_MAXIMUM_LENGTH],
                NULL AS [CHARACTER_OCTET_LENGTH],
                CAST(10 AS smallint) AS [NUMERIC_PRECISION],
                CAST(NULL AS smallint) AS [NUMERIC_SCALE],
                NULL AS [DESCRIPTION],
                'int' AS [TYPE_NAME],
                'int' AS [LOCAL_TYPE_NAME]
            UNION ALL
            SELECT
                DB_NAME() AS [PROCEDURE_CATALOG],
                SCHEMA_NAME(sp.schema_id) AS [PROCEDURE_SCHEMA],
                NULL AS [PROCEDURE_NAME],
                param.name AS [PARAMETER_NAME],
                param.parameter_id AS [ORDINAL_POSITION],
                CAST(CASE WHEN param.is_output = 1 THEN 2 ELSE 1 END AS smallint) AS [PARAMETER_TYPE],
                0 AS [PARAMETER_HASDEFAULT],
                NULL AS [PARAMETER_DEFAULT],
                CAST(1 AS bit) AS [IS_NULLABLE],
                0 AS [DATA_TYPE],
                CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [CHARACTER_MAXIMUM_LENGTH],
                NULL AS [CHARACTER_OCTET_LENGTH],
                CAST(param.precision AS smallint) AS [NUMERIC_PRECISION],
                CAST(param.scale AS smallint) AS [NUMERIC_SCALE],
                NULL AS [DESCRIPTION],
                ISNULL(baset.name, N'') AS [TYPE_NAME],
                ISNULL(baset.name, N'') AS [LOCAL_TYPE_NAME]
            FROM
                sys.all_objects AS sp
                INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
                LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id
            WHERE
                (sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(sp.name=@CommandName and SCHEMA_NAME(sp.schema_id)=@SchemaName)
            ORDER BY
                5 ASC

修改架构名称和存储过程名称后执行结果类似

ETMCDB dbo NULL @RETURN_VALUE 0 4 0 NULL 0 0 NULL NULL 10 NULL NULL int int

ETMCDB dbo NULL @OrderId 1 1 0 NULL 1 0 4 NULL 10 0 NULL int int

ETMCDB dbo NULL @DetailId 2 1 0 NULL 1 0 4 NULL 10 0 NULL int int

内容摘自

http://www.cnblogs.com/over140/archive/2008/12/23/1360150.html

转载于:https://www.cnblogs.com/kkun/archive/2009/02/05/1384381.html