笔记299 获取传入存储过程里的参数包括参数值 --2013-6-18

 1 --获取传入存储过程里的参数包括参数值 --2013-6-18
 2 http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/016071c4-4d1c-4cf2-8650-58ac6258e5dc
 3 
 4 SELECT * FROM sys.[parameters] --获取当前use的数据库里所有存储过程里的传入参数,但是不能获取参数值
 5 USE [pratice]
 6 GO
 7 ---------------------------------
 8 
 9 DROP FUNCTION [dbo].[f_split]
10 GO
11 CREATE FUNCTION f_split
12 (
13   @SourceSql VARCHAR(8000) ,
14   @StrSeprate VARCHAR(10)
15 )
16 RETURNS @temp TABLE ( ss VARCHAR(100) )
17 AS
18     BEGIN
19         DECLARE @i INT
20         SET @SourceSql = RTRIM(LTRIM(@SourceSql))
21         SET @i = CHARINDEX(@StrSeprate, @SourceSql)
22         WHILE @i >= 1
23             BEGIN
24                 INSERT  @temp
25                 VALUES  ( LEFT(@SourceSql, @i - 1) )
26                 SET @SourceSql = SUBSTRING(@SourceSql, @i + 1,
27                                            LEN(@SourceSql) - @i)
28                 SET @i = CHARINDEX(@StrSeprate, @SourceSql)
29             END
30         IF @SourceSql <> '\'
31             INSERT  @temp
32             VALUES  ( @SourceSql )
33         RETURN
34     END
35 
36 
37 ------------------------------------
38 
39 CREATE   proc up_test (@a varchar(100))
40 AS
41 DECLARE @s VARCHAR(200)
42 SET @s=' '
43 DECLARE @num INT
44 DECLARE @increase INT
45 DECLARE @temps VARCHAR(20)
46 CREATE TABLE temptb(id INT IDENTITY(1,1) NOT NULL,para VARCHAR(20))
47 INSERT INTO temptb(para) SELECT ss FROM [f_split](@a,',')
48 SELECT  @num = COUNT(*)
49 FROM    temptb
50 SET @increase = 1
51 WHILE @increase < @num +1
52     BEGIN
53         SELECT  @temps = para
54         FROM    temptb
55         WHERE   id = @increase;
56         SET @s = @s +' 参数'+CAST(@increase AS VARCHAR(20))+':  '+ @temps+';'
57         SET @increase = @increase + 1
58     END
59 SELECT  @s
60 DROP TABLE temptb
61 
62 exec up_test '库存,6,2013-6-12'