笔记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'
