22,210
社区成员
发帖
与我相关
我的任务
分享
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
create proc P1(@P1 int,@P2 nvarchar(100))
as
select p1=@P1,P2=@P2
go
exec p1 1,'a'
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),
@tbname varchar(100)--表名得有吧
as
begin
exec('select '+@p1+','+@p2+','+@p3+' from ['+@tbname+']')
end
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
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
exec('select * from ['+@tbname+']')
exec('select '+@p1+','+@p2+','+@p3+' from ['+@tbname+']')