SQL Server 2005元数据搜索以获取存储过程参数

发布于 2024-09-15 03:46:18 字数 65 浏览 3 评论 0原文

如何在 SQL Server 2005 中使用元数据获取存储过程的输入参数列表？我想对其进行选择，然后迭代变量名称。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

南渊 2024-09-22 03:46:18

从这里开始

select * from information_schema.PARAMETERS
where SPECIFIC_NAME = 'YourStoredProcedureName'
order by ORDINAL_POSITION

查找parameter_name、data_type、parameter_mode

start here

select * from information_schema.PARAMETERS
where SPECIFIC_NAME = 'YourStoredProcedureName'
order by ORDINAL_POSITION

look for parameter_name, data_type, parameter_mode

回复收藏 0 原文

锦欢 2024-09-22 03:46:18

SQL-Server本机方式：

select      type_name(system_type_id) as type_name
              , parameter_id
              , name
              , max_length
from          sys.parameters
where        object_id = object_id(N'{schema}.{name}');

SQL-Server native way:

select      type_name(system_type_id) as type_name
              , parameter_id
              , name
              , max_length
from          sys.parameters
where        object_id = object_id(N'{schema}.{name}');

回复收藏 0 原文

一抹淡然 2024-09-22 03:46:18

踢屁股！！！！！

我用它来生成 ADO 参数以完成经典的 ASP 苦差事。你节省了我几个小时！
更改 PROCEDURE dbo.createParms @procname VARCHAR(200) as

SELECT 'dbCommand.Parameters.Append (dbCommand.CreateParameter("'+ parameter_name + '"' + 
    ',' 
    + CASE WHEN data_type='char' THEN 'adChar'
    WHEN data_type='varchar' THEN 'adVarChar'
    WHEN data_type='int' THEN 'adInteger'
    WHEN data_type='text' THEN 'adLongVarchar'
    WHEN data_type='datetime' THEN 'adDBTimeStamp'
    WHEN data_type='bit' THEN 'adBoolean'
    WHEN data_type='uniqueidentifier' THEN 'adGuid'
    end
    +',adParamInput,'
    +CASE WHEN data_type='int' THEN '0' 
        WHEN  data_type='datetime' THEN '0'
        WHEN data_type='varchar' THEN CONVERT(VARCHAR,character_maximum_length)
        WHEN data_type='char' THEN CONVERT(VARCHAR,character_maximum_length)
        WHEN data_type='text' THEN 'len('+replace(parameter_name,'@','')+')'
        WHEN data_type='bit' THEN '0'
        WHEN data_type='uniqueidentifier' THEN '0'
     end
    + ','+replace(parameter_name,'@','') +'))' AS parm
from information_schema.PARAMETERS
where SPECIFIC_NAME = @procname

Kick ass!!!!!

I was using it to generate ADO parameters for classic asp drudgery. You saved me hours!!
alter PROCEDURE dbo.createParms @procname VARCHAR(200) as

SELECT 'dbCommand.Parameters.Append (dbCommand.CreateParameter("'+ parameter_name + '"' + 
    ',' 
    + CASE WHEN data_type='char' THEN 'adChar'
    WHEN data_type='varchar' THEN 'adVarChar'
    WHEN data_type='int' THEN 'adInteger'
    WHEN data_type='text' THEN 'adLongVarchar'
    WHEN data_type='datetime' THEN 'adDBTimeStamp'
    WHEN data_type='bit' THEN 'adBoolean'
    WHEN data_type='uniqueidentifier' THEN 'adGuid'
    end
    +',adParamInput,'
    +CASE WHEN data_type='int' THEN '0' 
        WHEN  data_type='datetime' THEN '0'
        WHEN data_type='varchar' THEN CONVERT(VARCHAR,character_maximum_length)
        WHEN data_type='char' THEN CONVERT(VARCHAR,character_maximum_length)
        WHEN data_type='text' THEN 'len('+replace(parameter_name,'@','')+')'
        WHEN data_type='bit' THEN '0'
        WHEN data_type='uniqueidentifier' THEN '0'
     end
    + ','+replace(parameter_name,'@','') +'))' AS parm
from information_schema.PARAMETERS
where SPECIFIC_NAME = @procname

回复收藏 0 原文

~没有更多了~