Z
zhuthesea
Unregistered / Unconfirmed
GUEST, unregistred user!
小弟在寫一個解釋器,為了執行存儲過程,想用TADOQuery如何查詢數據庫中的存儲過程所需參數,便於傳值,如果做呢?
我
我为编程狂
Unregistered / Unconfirmed
GUEST, unregistred user!
直接使用控件就可以了。
M
man8888
Unregistered / Unconfirmed
GUEST, unregistred user!
首先，如果你的存储过程是Oracle的，那么建议不要放在包（Package）里面，否则调用格式必须用带花括号{}括起来，非常麻烦！<br>通过ADOQuery调用存储过程可以这么调：<br> with ADOQuery1 do<br> begin<br> Parameters.Clear;<br> Parameters.ParamByName('Param1').Value:= 'Value1';<br> Prepared:= true;<br> Open; <br> end;
Z
zhuthesea
Unregistered / Unconfirmed
GUEST, unregistred user!
我知道可以通過SQL查詢數據庫表的信息一些字段名和數據類型大小等等，但現在想查詢數據庫中存儲過程中的信息，有辦法嗎，必須這樣做，
P
power255
Unregistered / Unconfirmed
GUEST, unregistred user!
MS-SQL Server<br>select specific_catalog 库名,<br> specific_schema 用户名,<br> specific_name 名称,<br> parameter_name 参数名,<br> data_type 类型,<br> ordinal_position 位置<br> from INFORMATION_SCHEMA.PARAMETERS<br> where specific_name = 存储过程名<br>Oracle <br>SELECT * FROM USER_ARGUMENTS
Z
zhuthesea
Unregistered / Unconfirmed
GUEST, unregistred user!
接受答案了.
Z
zhuthesea
Unregistered / Unconfirmed
GUEST, unregistred user!
Schema: How do I show the parameters for a function or stored procedure?<br>Let's say you have the following procedure: <br> <br>CREATE PROCEDURE dbo.fakeProcedure <br> @foo INT = 5, <br> @bar DATETIME = GETDATE, <br> @dec DECIMAL(19,2), <br> @x TEXT, <br> @vc VARCHAR(255) OUTPUT <br>AS <br>BEGIN <br> DECLARE @ret INT <br> IF @foo > 10 <br> SET @ret = 10 - @foo <br> ELSE <br> SET @ret = 10 + @foo <br> RETURN @ret <br>END <br>GO <br> <br>Now, you're planning on calling this stored procedure in your ASP code or in an application, and you forget the parameter names and/or their datatypes. <br> <br>Well, the first thing you could do is look at the second resultset of sp_help: <br> <br>EXEC sp_help 'fakeProcedure' <br> <br>However, this resultset does not differentiate between INPUT / OUTPUT parameters, nor does it show default values. <br> <br>There are a couple of built-in stored procedures that go into much greater detail about each parameter: <br> <br>EXEC sp_sproc_columns 'fakeProcedure' <br> <br>--or the undocumented/unsupported: <br> <br>EXEC sp_procedure_params_rowset 'fakeProcedure' <br> <br>These give far more information than necessary, and still don't show default values for applicable params. <br> <br>You can also look at the syscolumns table: <br> <br>SELECT * FROM syscolumns <br> WHERE id = OBJECT_ID('fakeProcedure') <br> <br>Again, default values are not returned. <br> <br>Finally, you could create your own procedure using the PARAMETERS view under INFORMATION_SCHEMA, to give you a much more concise result: <br> <br>CREATE PROCEDURE dbo.listProcParams <br> @proc_name SYSNAME <br>AS <br>BEGIN <br> SELECT <br> [Param] = COALESCE(PARAMETER_NAME, '<no params>'), <br> [DataType] = COALESCE(UPPER(DATA_TYPE) + CASE <br> WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN <br> '(' + CAST(NUMERIC_PRECISION AS VARCHAR) <br> + ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')' <br> WHEN RIGHT(DATA_TYPE, 4) = 'CHAR' THEN <br> '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' <br> ELSE '' END + CASE PARAMETER_MODE <br> WHEN 'INOUT' THEN ' OUTPUT' ELSE ' ' END, '-') <br> FROM <br> INFORMATION_SCHEMA.PARAMETERS <br> WHERE <br> SPECIFIC_NAME = @proc_name <br> ORDER BY <br> ORDINAL_POSITION <br>END <br>GO <br> <br>EXEC dbo.listProcParams 'fakeProcedure' <br> <br>Result: <br> <br>Param DataType <br>------ ---------------------- <br>@foo INT <br>@bar DATETIME <br>@dec DECIMAL(19, 2) <br>@x TEXT <br>@vc VARCHAR(255) OUTPUT <br> <br>Now, you'll notice if you run the following... <br> <br>SELECT * <br> FROM INFORMATION_SCHEMA.PARAMETERS <br> WHERE SPECIFIC_NAME='fakeProcedure' <br> <br>...that you *still* can't determine the default values. The sad truth is that these are simply not stored by SQL Server, they are merely evaluated by the text of the procedure at runtime. So, to determine default values, you're going to have to visually inspect the actual SP code, using sp_helptext or the ROUTINES view under INFORMATION_SCHEMA: <br> <br>EXEC sp_helptext 'fakeProcedure' <br> <br>--or <br> <br>SELECT ROUTINE_DEFINITION <br> FROM INFORMATION_SCHEMA.ROUTINES <br> WHERE ROUTINE_NAME = 'fakeProcedure' <br> <br>Please let us know if you have any tricks for determining default values programmatically (well, aside from brute force parsing, which is probably more work than resigning yourself to just look up the code for the rest of your life <G>
.<br>Related Articles<br> Schema: How do I list all the indexes in a database?<br> Schema: How do I list the databases on my server?<br> Schema: How do I show all the primary keys in a database?<br> Schema: How do I show all the triggers in a database?<br> Schema: How do I show the columns for a table?<br> Schema: How do I show the description property of a column?<br> Schema: How do I show the stored procedures in a database?<br> Schema: How do I show the tables in a database?<br> Schema: How do I show the user-defined functions (UDFs) in a database?<br> Schema: How do I show the views in a SQL Server database?
.<br>Related Articles<br> Schema: How do I list all the indexes in a database?<br> Schema: How do I list the databases on my server?<br> Schema: How do I show all the primary keys in a database?<br> Schema: How do I show all the triggers in a database?<br> Schema: How do I show the columns for a table?<br> Schema: How do I show the description property of a column?<br> Schema: How do I show the stored procedures in a database?<br> Schema: How do I show the tables in a database?<br> Schema: How do I show the user-defined functions (UDFs) in a database?<br> Schema: How do I show the views in a SQL Server database?