--这个是取得当前存储过程参数的,2005 SELECT params.NAME AS PARAM_NAME, params.PARAMETER_ID AS PARAM_ID, params.MAX_LENGTH AS PARAM_LENGTH, CASE params.SYSTEM_TYPE_ID WHEN 34 THEN 'IMAGE' WHEN 35 THEN 'TEXT' WHEN 48 THEN 'TINYINT' WHEN 52 THEN 'SMALLINT' WHEN 56 THEN 'INT' WHEN 58 THEN 'SMALLDATETIME' WHEN 59 THEN 'REAL' WHEN 60 THEN 'MONEY' WHEN 61 THEN 'DATETIME' WHEN 62 THEN 'FLOAT' WHEN 99 THEN 'NTEXT' WHEN 104 THEN 'BIT' WHEN 106 THEN 'DECIMAL' WHEN 122 THEN 'SMALLMONEY' WHEN 127 THEN 'BIGINT' WHEN 165 THEN 'VARBINARY' WHEN 167 THEN 'VARCHAR' WHEN 173 THEN 'BINARY' WHEN 175 THEN 'CHAR' WHEN 231 THEN 'NVARCHAR' WHEN 239 THEN 'NCHAR' ELSE 'MISSING' END AS PARAM_TYPE , params.IS_OUTPUT AS PARAM_OUTPUT FROM sys.parameters params,sys.objects obj WHERE params.OBJECT_ID = OBJECT_ID(N'p_test') ORDER BY params.PARAMETER_ID
第一步 提取存储过程中的所有参数。 第二步 填充到DataSet中(是存储过程所有的参数,而非结果),作为表的结构。或者 把存储过程中的所有参数,当做一张表的结构。 并非这样 exec('select '+@p1+','+@p2+','+@p3+' from ['+@tbname+']')这具体怎么实现???越简单越好。
是这样么?
如存储过程,
create proc p_test
@p1 nvarchar(100),
@p2 nvarchar(100),
@p3 nvarchar(100),
@p4 nvarchar(100),
@p5 nvarchar(100),
@p6 nvarchar(100),
@p7 nvarchar(100)
as
begin
...
end
楼主需要将@p1..@p7使用一个表返回来么?
还是什么意思?
Create Proc spTest @tb XML
AS DECLARE @rs table (id int)
INSERT INTO @rs
SELECT id.value('.')
FROM @tb.nodes('/root/id') as tmp(id).....
create proc p_test
@p1 nvarchar(100),
@p2 nvarchar(100),
@p3 nvarchar(100),
@p4 nvarchar(100),
@p5 nvarchar(100),
@p6 nvarchar(100),
@p7 nvarchar(100)
as
begin
...
end转换成可以这样
SELECT * FROM p_test
结果:
p1 p2 p3 p4 p5 p6 p7
Test1 Test2 Test3 Test4 Test5 Test6 Test7
as
select p1=@P1,@2=@P2
@p1 nvarchar(100),
@p2 nvarchar(100),
@p3 nvarchar(100),
@p4 nvarchar(100),
@p5 nvarchar(100),
@p6 nvarchar(100),
@p7 nvarchar(100),
@tbname varchar(100)--表名得有吧
as
begin
exec('select '+@p1+','+@p2+','+@p3+' from ['+@tbname+']')
end
create proc P1(@P1 int,@P2 nvarchar(100))
as
select p1=@P1,P2=@P2
goexec p1 1,'a'
Test1 Test2 Test3 Test4 Test5 Test6 Test7
这个数据从哪里来?
SELECT
params.NAME AS PARAM_NAME,
params.PARAMETER_ID AS PARAM_ID,
params.MAX_LENGTH AS PARAM_LENGTH,
CASE params.SYSTEM_TYPE_ID
WHEN 34 THEN 'IMAGE'
WHEN 35 THEN 'TEXT'
WHEN 48 THEN 'TINYINT'
WHEN 52 THEN 'SMALLINT'
WHEN 56 THEN 'INT'
WHEN 58 THEN 'SMALLDATETIME'
WHEN 59 THEN 'REAL'
WHEN 60 THEN 'MONEY'
WHEN 61 THEN 'DATETIME'
WHEN 62 THEN 'FLOAT'
WHEN 99 THEN 'NTEXT'
WHEN 104 THEN 'BIT'
WHEN 106 THEN 'DECIMAL'
WHEN 122 THEN 'SMALLMONEY'
WHEN 127 THEN 'BIGINT'
WHEN 165 THEN 'VARBINARY'
WHEN 167 THEN 'VARCHAR'
WHEN 173 THEN 'BINARY'
WHEN 175 THEN 'CHAR'
WHEN 231 THEN 'NVARCHAR'
WHEN 239 THEN 'NCHAR'
ELSE 'MISSING'
END AS PARAM_TYPE ,
params.IS_OUTPUT AS PARAM_OUTPUT
FROM
sys.parameters params,sys.objects obj
WHERE params.OBJECT_ID = OBJECT_ID(N'p_test')
ORDER BY params.PARAMETER_ID
第二步 填充到DataSet中(是存储过程所有的参数,而非结果),作为表的结构。或者
把存储过程中的所有参数,当做一张表的结构。
并非这样
exec('select '+@p1+','+@p2+','+@p3+' from ['+@tbname+']')这具体怎么实现???越简单越好。