June 6, 2012

sp_procedure_params_rowset2 (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_procedure_params_rowset2(nvarchar @procedure_schema
, nvarchar @parameter_name)

MetaData:

   
create procedure sys.sp_procedure_params_rowset2
(
@procedure_schema sysname = null,
@parameter_name sysname = null
)
as
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- copy & pasted from version 1 of the SProc and removed checks for 1st parameter !
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
select
PROCEDURE_CATALOG = s_pp.PROCEDURE_CATALOG,
PROCEDURE_SCHEMA = s_pp.PROCEDURE_SCHEMA,
PROCEDURE_NAME = convert(nvarchar(134),
s_pp.PROCEDURE_NAME +';'+
ltrim(str(coalesce(s_pp.procedure_number,1), 5))),
PARAMETER_NAME = s_pp.PARAMETER_NAME,
ORDINAL_POSITION = s_pp.ORDINAL_POSITION,
PARAMETER_TYPE = s_pp.PARAMETER_TYPE,
PARAMETER_HASDEFAULT = s_pp.PARAMETER_HASDEFAULT,
PARAMETER_DEFAULT = s_pp.PARAMETER_DEFAULT,
IS_NULLABLE = s_pp.IS_NULLABLE,
DATA_TYPE = s_pp.DATA_TYPE_28, -- for backward compatibility
CHARACTER_MAXIMUM_LENGTH = s_pp.CHARACTER_MAXIMUM_LENGTH_28, -- for backward compatibility
CHARACTER_OCTET_LENGTH = s_pp.CHARACTER_OCTET_LENGTH_28, -- for backward compatibility
NUMERIC_PRECISION = s_pp.NUMERIC_PRECISION,
NUMERIC_SCALE = s_pp.NUMERIC_SCALE,
DESCRIPTION = s_pp.DESCRIPTION,
TYPE_NAME = s_pp.TYPE_NAME_28, -- for backward compatibility
LOCAL_TYPE_NAME = s_pp.LOCAL_TYPE_NAME_28 -- for backward compatibility

from
sys.spt_procedure_params_view s_pp

where
(@procedure_schema is null or schema_id(@procedure_schema) = s_pp.schema_id)
and
(
(s_pp.type in ('P', 'PC')) or
(s_pp.procedure_number = 0 and s_pp.type in ('FN', 'TF', 'IF'))
) and
(@parameter_name is null or @parameter_name = s_pp.PARAMETER_NAME)

UNION ALL

select
PROCEDURE_CATALOG = s_pprv.PROCEDURE_CATALOG,
PROCEDURE_SCHEMA = s_pprv.PROCEDURE_SCHEMA,
PROCEDURE_NAME = convert(nvarchar(134),
s_pprv.PROCEDURE_NAME +';'+
ltrim(str(coalesce(s_pprv.procedure_number,1), 5))),
PARAMETER_NAME = s_pprv.PARAMETER_NAME,
ORDINAL_POSITION = s_pprv.ORDINAL_POSITION,
PARAMETER_TYPE = s_pprv.PARAMETER_TYPE,
PARAMETER_HASDEFAULT = s_pprv.PARAMETER_HASDEFAULT,
PARAMETER_DEFAULT = s_pprv.PARAMETER_DEFAULT,
IS_NULLABLE = s_pprv.IS_NULLABLE,
DATA_TYPE = s_pprv.DATA_TYPE, -- Return value is either int or empty.
CHARACTER_MAXIMUM_LENGTH = s_pprv.CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH = s_pprv.CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION = s_pprv.NUMERIC_PRECISION,
NUMERIC_SCALE = s_pprv.NUMERIC_SCALE,
DESCRIPTION = s_pprv.DESCRIPTION,
TYPE_NAME = s_pprv.TYPE_NAME,
LOCAL_TYPE_NAME = s_pprv.LOCAL_TYPE_NAME

from
sys.spt_procedure_params_return_values_view s_pprv

where
(@procedure_schema is null or schema_id(@procedure_schema) = s_pprv.schema_id) and
(
@parameter_name is null or
(@parameter_name = '@RETURN_VALUE' and s_pprv.type in ('P', 'PC')) or
(@parameter_name = '@TABLE_RETURN_VALUE' and s_pprv.type <> 'P' and s_pprv.type <> 'PC')
)

order by 2, 3, 5

No comments:

Post a Comment

Total Pageviews