Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.spt_procedure_params_view

 

This is a view in the mssqlsystemresource database.




Syntax

create view sys.spt_procedure_params_view
as
    select
    -- begin (for doing joins)
        object_id                   = o.object_id,
        schema_id                   = o.schema_id,
        procedure_number            = s_ap.procedure_number,
        type                        = o.type,
    -- end (for doing joins)
        PROCEDURE_CATALOG           = db_name(),
        PROCEDURE_SCHEMA            = schema_name(o.schema_id),
        PROCEDURE_NAME              = o.name,
        PARAMETER_NAME              = convert(sysname,
                                            case
                                            when s_ap.parameter_id = 0 -- UDF return value
                                            then '@RETURN_VALUE'
                                            else s_ap.name
                                            end),
        ORDINAL_POSITION            = convert(smallint, s_ap.parameter_id),
        PARAMETER_TYPE              = convert(smallint,
                                            case
                                            when s_ap.parameter_id = 0 -- UDF return value
                                            then 4 -- DBPARAMTYPE_RETURNVALUE
                                            else 1+s_ap.is_output
                                            end),
        PARAMETER_HASDEFAULT        = convert(tinyint, 0),
        PARAMETER_DEFAULT           = convert(nvarchar(255),null),
        IS_NULLABLE                 = convert(bit,ColumnProperty(s_ap.object_id,s_ap.name,'AllowsNull')),
        DATA_TYPE                   = d.oledb_data_type,
        DATA_TYPE_90                = convert(smallint,
                                            case -- Enable 9.0 downlevel clients to see new types.
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                                then 130 -- DBTYPE_WSTR
                                            when d.oledb_data_type = 132 and s_ap.max_length = 0 then 128 -- Large UDT <--> DBTYPE_BYTES for 9.0 clients
                                            else d.oledb_data_type
                                            end),
        DATA_TYPE_28                = convert(smallint,
                                            case -- Enable 8.0 downlevel clients to see new types.
                                            when d.oledb_data_type = 132 then 128 -- DBTYPE_UDT <--> DBTYPE_BYTES
                                            when d.oledb_data_type = 141 then 130 -- DBTYPE_XML <--> DBTYPE_WSTR
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                                then 130 -- DBTYPE_WSTR
                                            else d.oledb_data_type
                                            end),
        CHARACTER_MAXIMUM_LENGTH    = convert(int,
                                            case
                                            when d.oledb_data_type in (129, 128) -- DBTYPE_STR/DBTYPE_BYTES
                                            then coalesce(d.column_size,s_ap.max_length)
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                            then coalesce(d.column_size,s_ap.max_length/2)
                                            when d.oledb_data_type = 132 --DBTYPE_UDT, for Large UDT the s_ap.max_length is already 0
                                            then coalesce(s_ap.max_length,case t.is_assembly_type when 1 then t.max_length end)
                                            else null
                                            end),
        CHARACTER_MAXIMUM_LENGTH_90 = convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 128) -- DBTYPE_STR/DBTYPE_BYTES
                                            then coalesce(d.column_size,s_ap.max_length)
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                            then coalesce(d.column_size,s_ap.max_length/2)
                                            when d.oledb_data_type = 132 --DBTYPE_UDT
                                            then coalesce(s_ap.max_length,case t.is_assembly_type when 1 then t.max_length end)
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                            then s_ap.precision
                                            else null
                                            end),
        CHARACTER_MAXIMUM_LENGTH_28 = convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 128) -- DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/image
                                                            (
                                                                case when s_ap.max_length = 0 then 2147483647
                                                                else s_ap.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then isnull(d.column_size, -- special case ntext
                                                            (
                                                                case when s_ap.max_length = 0 then 1073741823
                                                                else s_ap.max_length/2
                                                                end
                                                            ))
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                            then 1073741823 -- match ntext value
                                            when (d.oledb_data_type = 132 and s_ap.max_length = 0)-- Large UDT => image for non-SNAC clients
                                                then 2147483647 -- match image value
                                            when d.oledb_data_type = 132 -- Small UDT
                                                then coalesce(s_ap.max_length,case t.is_assembly_type when 1 then t.max_length end)
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                            then s_ap.precision
                                            else null
                                            end),
        CHARACTER_OCTET_LENGTH      = convert(int,
                                            case
                                            when d.oledb_data_type in (129, 128) -- DBTYPE_STR/DBTYPE_BYTES
                                            then coalesce(d.column_size,s_ap.max_length)
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                            then coalesce(d.column_size*2,s_ap.max_length)
                                            when d.oledb_data_type = 132 --DBTYPE_UDT, for Large UDT s_ap.max_length is already 0
                                            then coalesce(s_ap.max_length,case t.is_assembly_type when 1 then t.max_length end)
                                            else null
                                            end),
        CHARACTER_OCTET_LENGTH_90   = convert(int,
            case
                                            when d.oledb_data_type in (129, 128) -- DBTYPE_STR/DBTYPE_BYTES
                                            then coalesce(d.column_size,s_ap.max_length)
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                            then coalesce(d.column_size*2,s_ap.max_length)
                                            when d.oledb_data_type = 132 --DBTYPE_UDT
                                            then coalesce(s_ap.max_length,case t.is_assembly_type when 1 then t.max_length end)
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                            then s_ap.precision*2
                                            else null
                                            end),
        CHARACTER_OCTET_LENGTH_28   = convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 128) -- DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/image
                                                            (
                                                                case when s_ap.max_length = 0 then 2147483647
                                                                else s_ap.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then isnull(d.column_size*2, -- special case ntext
                                                            (
                                                                case when s_ap.max_length = 0 then 2147483646
                                                                else s_ap.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                            then 2147483646 -- match ntext value
                                            when (d.oledb_data_type = 132 and s_ap.max_length = 0)-- Large UDT => image for non-SNAC clients
                                                then 2147483647 -- match image value
                                            when d.oledb_data_type = 132 -- Small UDT
                                                then coalesce(s_ap.max_length,case t.is_assembly_type when 1 then t.max_length end)
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                            then s_ap.precision*2
                                            else null
                                            end),
        NUMERIC_PRECISION           = convert(smallint,
                                            case
                                            when d.oledb_data_type = 131 --DBTYPE_NUMERIC
                                            then s_ap.precision
                                            when (d.fixed_prec_scale = 1 or d.oledb_data_type = 5 or d.oledb_data_type = 4)
                                            then d.data_precision
                                            else null
                                            end),
        NUMERIC_SCALE               = convert(smallint,
                                            case
                                            when d.oledb_data_type = 131 --DBTYPE_NUMERIC
                                            then s_ap.scale
       else null
                                            end),
        DESCRIPTION                 = convert(nvarchar(1),null),
        TYPE_NAME                   = convert(sysname,
                                      case
                                      when (s_ap.user_type_id <= 256)
                                        then d.type_name
                                      
                                      -- CLR UDT and table types emit type name
                                      
                                      when (t.is_assembly_type = 1 or t.is_table_type = 1)
                                        then t.name
                                      end) collate database_default,
        TYPE_NAME_28                = convert(sysname,
                                      case
                                      when (d.oledb_data_type = 128 and s_ap.max_length = 0) -- varbinary(max)
                                        then N'image'
                                      when (d.oledb_data_type = 129 and s_ap.max_length = 0) -- varchar(max)
                                        then N'text'
                                      when (d.oledb_data_type = 130 and s_ap.max_length = 0) -- nvarchar(max)
                                        then N'ntext'
                                      when (d.oledb_data_type = 132 and s_ap.max_length = 0) -- Large UDT => image for non-SNAC clients
                                        then N'image'
                                      else d.type_name
                                      end) collate database_default,
        LOCAL_TYPE_NAME             = convert(sysname,
                                      case
                                      when (s_ap.user_type_id <= 256)
                                        then d.localized_typename
                                      
                                      -- CLR UDT and table types emit type name
                                      
                                      when (t.is_assembly_type = 1 or t.is_table_type = 1)
                                        then t.name
                                      end) collate database_default,
        LOCAL_TYPE_NAME_28          = convert(sysname,
                                      case
                                      when (d.oledb_data_type = 128 and s_ap.max_length = 0) -- varbinary(max)
                                        then N'image'
                                      when (d.oledb_data_type = 129 and s_ap.max_length = 0) -- varchar(max)
                                        then N'text'
                                      when (d.oledb_data_type = 130 and s_ap.max_length = 0) -- nvarchar(max)
                                        then N'ntext'
                                      when (d.oledb_data_type = 132 and s_ap.max_length = 0) -- Large UDT => image for non-SNAC clients
                                        then N'image'
                                      else d.type_name
                                      end) collate database_default,
        SS_XML_SCHEMACOLLECTION_CATALOGNAME = convert(sysname,
                                            case when (d.ss_dtype = 241 and xsc.name is not null)
                                            then db_name()
                                            else null
                                            end),
        SS_XML_SCHEMACOLLECTION_SCHEMANAME = convert(sysname,
                                            case when (d.ss_dtype = 241 and xsc.name is not null)
                                            then schema_name(xsc.schema_id)
                                            else null
                                            end),
        SS_XML_SCHEMACOLLECTIONNAME = convert(sysname, xsc.name),
        SS_UDT_CATALOGNAME          = convert(sysname,
                                 case when s_ap.user_type_id > 256 and t.is_assembly_type = 1
                                            then db_name()
                                            else null
                                            end),
        SS_UDT_SCHEMANAME           = convert(sysname,
                                            case when s_ap.user_type_id > 256 and t.is_assembly_type = 1
                                            then schema_name(t.schema_id)
                                            else null
                                            end),
        SS_UDT_NAME                 = convert(sysname,
                                            case when s_ap.user_type_id > 256 and t.is_assembly_type = 1
                                              then t.name
                                            else null
                                            end),
        SS_UDT_ASSEMBLY_TYPENAME    = case when s_ap.user_type_id > 256 and t.is_assembly_type = 1
                      then convert(nvarchar(4000), typepropertyex(t.user_type_id,'assembly_qualified_name')) collate Latin1_General_BIN
                                      else null
                                      end,
        
        -- New columns introduced in Katmai.
        -- SS_TYPE_CATALOG_NAME, SS_TYPE_SCHEMANAME apply to XML schema, CLR UDT, and table types
        -- We will recommend new apps to use these columns for all such types.
        
        SS_TYPE_CATALOG_NAME        = convert(sysname,
                                            case
                                            when (s_ap.user_type_id > 256 and (t.is_assembly_type = 1 or t.is_table_type = 1))
                                              then db_name()
                                            when (d.ss_dtype = 241 and xsc.name is not null)
                                              then db_name()
                                            else null
                                            end),
        SS_TYPE_SCHEMANAME          = convert(sysname,
                                            case
                                            when (s_ap.user_type_id > 256 and (t.is_assembly_type = 1 or t.is_table_type = 1))
                                              then schema_name(t.schema_id)
                                            when (d.ss_dtype = 241 and xsc.name is not null)
                                              then schema_name(o.schema_id)
                                            else null
                                            end),
        MANAGED_DATA_TYPE           = d.managed_type_code,
        SS_DATETIME_PRECISION       = convert(int,
                                            case when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                            then s_ap.scale
                                            else d.datetime_precision
                                            end)
    from
        sys.all_objects o inner join
        sys.spt_parameters_numbered_parameters s_ap on
            (
                o.object_id = s_ap.object_id and
                o.type in ('P', 'FN', 'TF', 'IF')
            ) inner join
        sys.types t on
            (
                s_ap.user_type_id = t.user_type_id
            ) inner join
        sys.spt_provider_types d on
            (
                d.ss_dtype = t.system_type_id
            )
        left join
        sys.xml_schema_collections xsc on
            (
                xsc.xml_collection_id = s_ap.xml_collection_id
            )

 
Last revision 2008RTM
See also

  sp_procedure_params_90_rowset (Procedure)
sp_procedure_params_90_rowset2 (Procedure)
sp_procedure_params_rowset (Procedure)
sp_procedure_params_rowset2 (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash